0
0
Power BIbi_tool~10 mins

Role-playing dimensions in Power BI - Cell-by-Cell Formula Trace

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

Sales orders data with OrderDate and ShipDate as two roles of the Date dimension.

CellValue
A1OrderID
B1OrderDate
C1ShipDate
D1CustomerID
E1Amount
A21001
B22024-01-01
C22024-01-05
D2C001
E2250
A31002
B32024-01-02
C32024-01-06
D3C002
E3450
A41003
B42024-01-03
C42024-01-07
D4C001
E4300
Formula Trace
Total Sales by Order Date = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[OrderDate], Date[Date]))
Step 1: SUM(Sales[Amount])
Step 2: USERELATIONSHIP(Sales[OrderDate], Date[Date])
Step 3: CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[OrderDate], Date[Date]))
Cell Reference Map
    A        B           C          D         E
1 |OrderID|OrderDate | ShipDate |CustomerID| Amount |
2 | 1001  |2024-01-01|2024-01-05|   C001   |  250   |
3 | 1002  |2024-01-02|2024-01-06|   C002   |  450   |
4 | 1003  |2024-01-03|2024-01-07|   C001   |  300   |
The formula uses the Sales table columns OrderDate and Amount. The USERELATIONSHIP activates the relationship between Sales[OrderDate] and Date[Date].
Result
Date       | Total Sales by Order Date
2024-01-01 | 250
2024-01-02 | 450
2024-01-03 | 300
The result shows total sales amounts grouped by OrderDate using the role-playing Date dimension activated by USERELATIONSHIP.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does USERELATIONSHIP do in this formula?
ASums the Amount column
BFilters the CustomerID column
CActivates the relationship between Sales[OrderDate] and Date[Date]
DCreates a new column in the table
Key Result
USERELATIONSHIP activates an inactive relationship to use a specific role-playing dimension in CALCULATE.