Ravindra BagaleCourses & study guides Track your progress

Guides

USERELATIONSHIP in DAX — Use Inactive Relationships

By , Power BI & Data Analytics trainer LinkedIn Published: Last updated:

USERELATIONSHIP activates an inactive model relationship inside CALCULATE for that measure only — the standard fix when you need sales by OrderDate and by DeliveredDate without two active paths between the same tables.

Chala mitrano! Model view shows a solid line and a dashed line — students think the dashed one is “broken”. Why? Power BI allows one active path. How? We keep OrderDate active, then write a DeliveredDate measure with USERELATIONSHIP on fictional FreshBasket dates. Clean close for this batch.

Quick answer

Vertical path:

  1. Model view: active Sales[OrderDate] → Date[Date].
  2. Inactive: Sales[DeliveredDate] → Date[Date] (dashed).
  3. Normal sales use the active path.
  4. Sales by Delivery = CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[DeliveredDate], 'Date'[Date] ) ).
  5. Compare both measures under the same Year slicer.
  6. Do not fight Desktop for two active relationships — use measures.
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Sales[DeliveredDate], 'Date'[Date] )
)

Why USERELATIONSHIP, rows in / number out

Order ID OrderDate DeliveredDate Amount City
A 5 Jan 8 Jan 100 Mumbai
B 28 Jan 2 Feb 40 Mumbai
C 10 Feb 12 Feb 80 Nashik
  1. Why: one rupee can sit in January by order date and February by delivery date.
  2. Active path = OrderDate. January OrderDate filter: rows A + B. Number out: 140 (Mumbai’s January orders).
  3. Delivery measure activates the DeliveredDate path. January delivery filter: only A (100). Order B delivered in February.
  4. Same [Total Sales] expression. Different relationship. Different month story.
  5. Titles must say which clock you used.
Inactive relationship Only one active path; the dashed line is inactive until USERELATIONSHIP. Sales Date OrderDate active DeliveredDate inactive model

Model view: one active path (solid); a second date path is often inactive (dashed) until a measure activates it.

What do I need before this guide?

Before and after (look at the tables first)

Before USERELATIONSHIP Active ShipDate path; March ship rows.

Before

After USERELATIONSHIP OrderDate path; March ordered amount 40.

After

Inactive is by design

  1. Two date columns on Sales both want Date.
  2. Only one relationship stays active.
  3. The other waits for USERELATIONSHIP in a measure.
  4. Not an error — a modelling rule.

Shop analogy: one wall calendar is the “order clock”. A second calendar is the “delivery clock”. Only one is pinned as default. USERELATIONSHIP borrows the delivery calendar for one question.

USERELATIONSHIP in CALCULATE

USERELATIONSHIP in CALCULATE Activate the inactive path only for this measure. CALCULATE USERELATIONSHIP( Delivered, Date ) Sales activate

Pattern: CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[DeliveredDate], 'Date'[Date] ) ).

Sales by Delivery =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP (
        Sales[DeliveredDate],
        'Date'[Date]
    )
)
  1. Activates the delivered-date path for this calculation.
  2. Year/Month filters then apply along delivered dates.
  3. Active OrderDate path remains the default for other measures.

Business question first

Order date vs delivered date Same sales, two timelines — pick the relationship the question needs. By OrderDatewhen sold By DeliveredDateUSERELATIONSHIP when

Business question first: “sales by order date” vs “sales by delivered date” — then activate the matching relationship.

  1. “When did customers order?” → OrderDate (active).
  2. “When did we deliver?” → DeliveredDate via USERELATIONSHIP.
  3. Same rupees can sit in different months on the two timelines.
  4. Titles must say which clock you used.

Mistakes and calm fixes

Symptom Likely cause Fix
“Relationship doesn’t work” It is inactive Use USERELATIONSHIP in the measure
Blank measure Wrong columns paired Match DeliveredDate to Date[Date]
Same as normal sales Still on active path Confirm USERELATIONSHIP arguments
Model error fighting two actives Tried to activate both Keep one active; measure the rest

Ghabru naka 😅 — dashed lines are invitations, not failures.

Ravindra Bagale's Tip

Many students create a second active relationship, get a warning, and delete the path. Better story: leave it inactive, activate with USERELATIONSHIP when the question is delivery-dated. Pay attention!

Practice task

  1. Confirm active vs inactive date paths in Model view.
  2. Create Sales by Delivery with USERELATIONSHIP.
  3. Month matrix: normal Sales vs Sales by Delivery (January order 140 vs delivery 100 on the sample idea).
  4. Find a month where they differ; explain why.
  5. Rename visuals “by order date” / “by delivered date”.

Learn it properly

Course lessons:

Related guides: RELATED · % of total

Samajla ka? One active path; USERELATIONSHIP wakes an inactive one inside CALCULATE. Guides 11–30 classroom rewrite complete for this batch. Aata pudhe jaauya.

Frequently asked questions

What does USERELATIONSHIP do?

Inside CALCULATE, it activates an inactive relationship for that evaluation only.

Why are relationships inactive?

Power BI allows one active relationship path between two tables; extra paths are inactive by design.

Can I make both active?

Not between the same two tables at once. Use USERELATIONSHIP in measures for the alternate path.

Blank results?

Wrong columns paired, missing dates, or relationship not actually defined in the model.

ROLE-playing dates?

Order vs ship vs deliver dates are classic role-playing date scenarios — this pattern is the standard fix.

Course lessons?

USERELATIONSHIP in DAX and relationships in data modelling.