Role-Playing Dimensions
Power BI · Data Modeling
Theory
A role-playing dimension is one dimension table used for more than one purpose. The classic case is Date: a Sales table might have both OrderDate and ShipDate, and both should be analysed by the same calendar. Power BI allows only one active relationship between two tables, so you create 'Date'[Date] → Sales[OrderDate] as active and 'Date'[Date] → Sales[ShipDate] as inactive (shown as a dashed line).
Measures use the active relationship automatically. To analyse by ship date, write a measure that activates the inactive one with USERELATIONSHIP inside CALCULATE. For example, suppose our Sales table also had a ShipDate column; order 103 was placed on 20 Feb but shipped on 2 Mar. By order date, February has ₹2,300 of sales; by ship date February has only ₹800 and March rises to ₹3,700. The alternative is to load a second copy of the Date table (Ship Date) with its own active relationship.
Why we use it
Businesses ask the same question through different dates: what was ordered versus shipped versus delivered. Role-playing dimensions answer all of them from one calendar without duplicating fact data.
When to use it
Use inactive relationships plus USERELATIONSHIP when you need a few measures by the second date. Use a separate copy of the dimension when users need to slice by both dates independently in the same visual or with slicers.
How it works
- Fact table has two date columns
- Create the active relationship on OrderDate
- Create an inactive relationship on ShipDate
- Must users slice by both dates independently? Yes: Add a second copy of the Date table. No: Write USERELATIONSHIP measures.
- Analyse by order date and ship date
Syntax
Model: 'Date'[Date] → Sales[OrderDate] (active)
'Date'[Date] → Sales[ShipDate] (inactive)
DAX:
USERELATIONSHIP(<columnName1>, <columnName2>) // only inside CALCULATE / CALCULATETABLEExample
// Assume Sales also has a ShipDate column
Sales by Ship Date =
CALCULATE(
[Total Sales],
USERELATIONSHIP('Date'[Date], Sales[ShipDate])
)
// Visual: 'Date'[Month] with [Total Sales] and [Sales by Ship Date]Key points
- Only one relationship between two tables can be active
- Inactive relationships appear as dashed lines in Model view
- USERELATIONSHIP switches to the inactive one inside CALCULATE
- Alternative: duplicate the dimension (Order Date, Ship Date tables)
- Totals across all months stay the same; only the timing shifts