3.1.3. Role-Playing Dimensions

💡 First Principle: One dimension can play several roles for the same fact. An order has an order date, a ship date, and a due date, all describing time, all pointing to the same Date table. Because only one relationship between two tables can be active, you must choose how each role gets its own filter path.

When you create three relationships from Sales to Date (OrderDateKey, ShipDateKey, DueDateKey), one becomes active (solid line) and the others inactive (dashed). Filters from a Date slicer travel only through the active one. You have two patterns:

Pattern 1: inactive relationships with USERELATIONSHIP. Keep one Date table. Write measures that activate the needed relationship temporarily:

Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Sales[ShipDateKey], 'Date'[DateKey] )
)

Pros: one date table, one slicer filters every measure by the chosen role. Cons: you need a separate measure for each role, and report authors must pick the right measure.

Pattern 2: duplicate the dimension per role. Create Order Date, Ship Date, and Due Date tables (by referencing the Date query in Power Query, or as calculated tables), each with an active relationship. Pros: authors can slice by any role directly and use the same measure (e.g., Total Sales by Ship Date[Month]). Cons: more tables, larger model, and separate slicers per role. Rename columns in each copy (Ship Month, Ship Year) so authors can tell them apart.

Choose Pattern 1 when most analysis uses one role and others are occasional comparisons; choose Pattern 2 when users need to slice and filter freely by each role at the same time ("orders placed in March that shipped in April").

⚠️ Exam Trap: USERELATIONSHIP only works inside CALCULATE (or functions that take filter arguments) and only activates a relationship that already exists as inactive in the model. It can't create a relationship, and you can't make two relationships between the same tables active simultaneously.

Reflection Question: Users need a matrix showing orders by order month on rows and ship month on columns. Why does this requirement push you toward duplicated role-playing tables rather than USERELATIONSHIP measures?