0
0
Power BIbi_tool~10 mins

Active vs inactive relationships in Power BI - Formula Comparison Trace

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

This data shows dates in a calendar table (A), sales dates (B), ship dates (C), and sales amounts (D). The model has two relationships from the calendar to sales: one active on SalesDate and one inactive on ShipDate.

CellValue
A1Date
A22024-01-01
A32024-01-02
A42024-01-03
B1SalesDate
B22024-01-01
B32024-01-02
B42024-01-03
C1ShipDate
C22024-01-02
C32024-01-03
C42024-01-04
D1SalesAmount
D2100
D3150
D4200
Formula Trace
Total Sales by Ship Date = CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Calendar[Date], Sales[ShipDate]))
Step 1: SUM(Sales[SalesAmount])
Step 2: USERELATIONSHIP(Calendar[Date], Sales[ShipDate])
Step 3: CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Calendar[Date], Sales[ShipDate]))
Cell Reference Map
Date
↓
Date
The formula uses the inactive relationship between Calendar Date and Sales ShipDate, which is normally inactive but activated temporarily by USERELATIONSHIP.
Result
   E
1 Total Sales by Ship Date
2 250
The result shows total sales amount filtered by ShipDate using the inactive relationship activated by USERELATIONSHIP, summing 100 + 150 = 250.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does USERELATIONSHIP(Calendar[Date], Sales[ShipDate]) do in the formula?
AActivates the inactive relationship between Calendar Date and ShipDate
BDeactivates the active relationship between Calendar Date and SalesDate
CFilters sales by SalesDate
DIgnores all relationships
Key Result
USERELATIONSHIP activates an inactive relationship temporarily inside CALCULATE to filter data differently.