Role-playing dimensions in Power BI - Dashboard Guide

Dashboard Mode - Role-playing dimensions

Business Question

How can we analyze sales data by different date roles like Order Date and Ship Date using the same Date dimension?

Sample Data

OrderID	OrderDate	ShipDate	Product	SalesAmount
1001	2024-01-05	2024-01-10	Widget A	250
1002	2024-01-07	2024-01-12	Widget B	450
1003	2024-01-10	2024-01-15	Widget A	300
1004	2024-01-12	2024-01-18	Widget C	500
1005	2024-01-15	2024-01-20	Widget B	400

DateKey	Date	Year	Month	Day
20240105	2024-01-05	2024	January	5
20240107	2024-01-07	2024	January	7
20240110	2024-01-10	2024	January	10
20240112	2024-01-12	2024	January	12
20240115	2024-01-15	2024	January	15
20240118	2024-01-18	2024	January	18
20240120	2024-01-20	2024	January	20

Dashboard Components

• KPI Card: Total Sales by Order Date
  Formula: Total Sales OrderDate = SUM(Sales[SalesAmount])
  This shows total sales filtered by the Order Date role of the Date dimension.
• KPI Card: Total Sales by Ship Date
  Formula: Total Sales ShipDate = CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Date[Date]))
  This calculates total sales using the Ship Date role of the Date dimension.
• Line Chart: Sales Over Time by Order Date
  Axis: Date[Date]
  Values: Total Sales OrderDate measure
  Shows sales trend by order date.
• Line Chart: Sales Over Time by Ship Date
  Axis: Date[Date]
  Values: Total Sales ShipDate measure
  Shows sales trend by ship date.
• Table: Sales Details
  Columns: OrderID, Product, OrderDate, ShipDate, SalesAmount
  Shows raw sales data for reference.

Dashboard Layout

+---------------------------+---------------------------+
| Total Sales by Order Date | Total Sales by Ship Date  |
+---------------------------+---------------------------+
|                           |                           |
|  Line Chart: Sales by     |  Line Chart: Sales by     |
|  Order Date               |  Ship Date                |
|                           |                           |
+---------------------------+---------------------------+
|                           Sales Details Table                          |
+-----------------------------------------------------------------------+

Interactivity

Filters on the Date dimension will affect the charts and KPIs differently depending on the active relationship:

• When filtering by Order Date, the Total Sales by Order Date KPI and its chart update.
• When filtering by Ship Date, the Total Sales by Ship Date KPI and its chart update.
• The Sales Details Table always shows all sales rows and updates based on slicers for Product or OrderID.
• Using slicers on Date with USERELATIONSHIP in measures allows switching between date roles without duplicating the Date table.

Self Check

If you add a filter selecting dates from 2024-01-10 to 2024-01-15 on the Date dimension:

• Which KPI card updates to show sales only for orders placed in that date range?
• Which KPI card updates to show sales only for shipments in that date range?
• How does the Sales Details Table change?

Key Result

Dashboard showing sales analysis by Order Date and Ship Date using role-playing Date dimension.