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.