Role-playing dimensions in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report showing sales performance by order date and ship date separately, using the same Date dimension table.

📊 Data: You have a Sales table with OrderDateKey and ShipDateKey columns referencing a Date dimension table. The Date table contains all dates with attributes like Year, Month, and Day.

🎯 Deliverable: Create a Power BI report that uses the Date dimension twice as role-playing dimensions: once for Order Date and once for Ship Date, showing total sales by each date role.

Sample Data

SalesID	OrderDateKey	ShipDateKey	SalesAmount
1	20230101	20230103	100
2	20230102	20230105	150
3	20230101	20230104	200
4	20230103	20230106	120
5	20230102	20230105	180

DateKey	Date	Year	Month	Day
20230101	2023-01-01	2023	January	1
20230102	2023-01-02	2023	January	2
20230103	2023-01-03	2023	January	3
20230104	2023-01-04	2023	January	4
20230105	2023-01-05	2023	January	5
20230106	2023-01-06	2023	January	6

1

Step 1: Load the Sales and Date tables into Power BI.

Import Sales and Date tables from your data source.

Expected Result

Sales and Date tables appear in Power BI data model.

2

Step 2: Create two relationships between Sales and Date tables: one for OrderDateKey and one for ShipDateKey.

Create relationship: Sales[OrderDateKey] -> Date[DateKey] (active) Create relationship: Sales[ShipDateKey] -> Date[DateKey] (inactive)

Expected Result

One active and one inactive relationship between Sales and Date tables.

3

Step 3: Create a measure to calculate total sales by Order Date using the active relationship.

Total Sales by Order Date = SUM(Sales[SalesAmount])

Expected Result

Measure sums SalesAmount filtered by OrderDateKey.

4

Step 4: Create a measure to calculate total sales by Ship Date using the inactive relationship activated with USERELATIONSHIP.

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

Expected Result

Measure sums SalesAmount filtered by ShipDateKey.

5

Step 5: Build a report with two visuals: one table showing total sales by Order Date, another showing total sales by Ship Date.

Visual 1: Table with Date[Date], Total Sales by Order Date Visual 2: Table with Date[Date], Total Sales by Ship Date

Expected Result

Two tables showing sales totals by different date roles.

Final Result

Sales Report by Role-Playing Dates

Order Date Sales:
Date       | Total Sales
2023-01-01 | 300
2023-01-02 | 330
2023-01-03 | 120

Ship Date Sales:
Date       | Total Sales
2023-01-03 | 100
2023-01-04 | 200
2023-01-05 | 330
2023-01-06 | 120

✓Sales totals differ when viewed by Order Date versus Ship Date.

✓Order Date shows when sales were made; Ship Date shows when products were shipped.

✓Using role-playing dimensions helps analyze the same data from different time perspectives.

Bonus Challenge

Add a slicer to the report to filter both Order Date and Ship Date visuals by Year and Month simultaneously.

Show Hint

Create disconnected Date tables or use synchronized slicers with DAX measures to apply the same filter to both date roles.

Practice

1. What is the main purpose of using role-playing dimensions in Power BI data models?

easy

A. To avoid using relationships between tables

B. To create multiple copies of the same table for different reports

C. To store measures separately from dimensions

D. To reuse a single dimension table for multiple roles in the model

Solution

1. Step 1: Understand role-playing dimension concept

   Role-playing dimensions allow one table to serve multiple roles, like Order Date and Ship Date, without duplicating tables.

2. Step 2: Identify the correct purpose

   This reuse simplifies the model and avoids redundancy.

3. Final Answer:

   To reuse a single dimension table for multiple roles in the model -> Option D

4. Quick Check:

   Role-playing dimension = reuse one table [OK]

Hint: Role-playing means reusing one table for different roles [OK]

Common Mistakes:

• Thinking role-playing means duplicating tables
• Confusing measures with dimensions
• Believing it removes relationships

2. Which DAX function is used to activate an inactive relationship in a role-playing dimension scenario?

easy

A. USERELATIONSHIP()

B. RELATED()

C. CALCULATE()

D. FILTER()

Solution

1. Step 1: Recall inactive relationships in role-playing dimensions

   Only one relationship can be active; others are inactive and need special handling.

2. Step 2: Identify the function to activate inactive relationships

   USERELATIONSHIP() activates an inactive relationship inside a CALCULATE() expression.

3. Final Answer:

   USERELATIONSHIP() -> Option A

4. Quick Check:

   Activate inactive relationship = USERELATIONSHIP() [OK]

Hint: Use USERELATIONSHIP() to activate inactive links [OK]

Common Mistakes:

• Using RELATED() which only fetches related values
• Confusing CALCULATE() as the activator alone
• Trying FILTER() to activate relationships

3. Given a Sales table linked to a Date table twice: once by OrderDate (active) and once by ShipDate (inactive). What does this DAX measure return?

Shipped Sales = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], Date[Date]))

medium

A. Sum of sales filtered by ShipDate

B. Error due to inactive relationship

C. Sum of all sales ignoring dates

D. Sum of sales filtered by OrderDate

Solution

1. Step 1: Understand the measure components

   The measure sums Sales[Amount] but uses USERELATIONSHIP() to activate the inactive ShipDate relationship.

2. Step 2: Determine the filter context

   This means the sum is filtered by ShipDate, not the active OrderDate relationship.

3. Final Answer:

   Sum of sales filtered by ShipDate -> Option A

4. Quick Check:

   USERELATIONSHIP activates ShipDate filter [OK]

Hint: USERELATIONSHIP switches filter to inactive date [OK]

Common Mistakes:

• Assuming active relationship always applies
• Thinking it sums all sales without filter
• Expecting an error from inactive relationship

4. You wrote this measure but it returns incorrect results:

Shipped Sales = CALCULATE(SUM(Sales[Amount]), Date[Date])

What is the likely problem?

medium

A. SUM() cannot be used inside CALCULATE()

B. Missing USERELATIONSHIP() to activate inactive ShipDate relationship

C. ShipDate table does not exist

D. CALCULATE() requires a filter expression, not a column

Solution

1. Step 1: Analyze the measure syntax

   The measure tries to filter by Date[Date] but does not activate the inactive relationship.

2. Step 2: Identify missing function for inactive relationship

   Without USERELATIONSHIP(), the inactive relationship is ignored, causing wrong results.

3. Final Answer:

   Missing USERELATIONSHIP() to activate inactive ShipDate relationship -> Option B

4. Quick Check:

   Inactive relationship needs USERELATIONSHIP() [OK]

Hint: Inactive relationships need USERELATIONSHIP() inside CALCULATE() [OK]

Common Mistakes:

• Thinking CALCULATE() can't use SUM()
• Assuming ShipDate table is missing
• Using a column instead of a filter expression

5. You have a Date table linked twice to Sales: OrderDate (active) and DeliveryDate (inactive). You want a measure to calculate total sales for deliveries in 2023 only. Which DAX measure correctly does this?

hard

A. Delivery Sales 2023 = SUM(Sales[Amount]) + FILTER(Date, Date[Year] = 2023)

B. Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), Date[Year] = 2023)

C. Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[DeliveryDate], Date[Date]), Date[Year] = 2023)

D. Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[OrderDate], Date[Date]), Date[Year] = 2023)

Solution

1. Step 1: Activate the correct inactive relationship

   Since DeliveryDate is inactive, USERELATIONSHIP(Sales[DeliveryDate], Date[Date]) is needed to filter by delivery dates.

2. Step 2: Apply the year filter correctly

   Adding Date[Year] = 2023 filters the Date table to year 2023, restricting sales to that year.

3. Final Answer:

   Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[DeliveryDate], Date[Date]), Date[Year] = 2023) -> Option C

4. Quick Check:

   Activate DeliveryDate + filter year 2023 = Delivery Sales 2023 = CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[DeliveryDate], Date[Date]), Date[Year] = 2023) [OK]

Hint: Use USERELATIONSHIP with year filter inside CALCULATE() [OK]

Common Mistakes:

• Forgetting USERELATIONSHIP for inactive DeliveryDate
• Filtering only by year without activating relationship
• Using wrong relationship (OrderDate instead of DeliveryDate)