Ravindra BagaleCourses & study guides मराठी Track your progress

11. Data Modelling

11.10 Role-Playing Dimensions

By Last updated:

Start with a problem

Shraddha's order placed on 31-Mar-2026 at 23:50 (IST) is delivered on 01-Apr-2026. Finance asks: FY 2025-26 or FY 2026-27?

Explain simply

One Date table can play two roles: order date and delivered date. Either copy it (a second role table) or keep one table with an inactive relationship and USERELATIONSHIP.

Show the data

Order ID Order Date Delivered Date City Amount (₹)
901 31-03-2026 01-04-2026 Pune 500
902 01-04-2026 01-04-2026 Pune 300
903 30-04-2026 01-05-2026 Nashik 200

Fictional sample data. Practice file: role_playing_dates.csv (source: src/practice/).

Demonstrate

  1. Load role_playing_dates.csv as Orders. Create Date = CALENDAR(DATE(2026,3,1), DATE(2026,5,31)) with a Month Name column; mark it as a date table.
  2. Relate Date[Date] 1 → * Orders[Order Date] (active).

  3. Modeling › New table Delivered Date = 'Date' › relate Delivered Date[Date] 1 → * Orders[Delivered Date].

    Your result should look like this:

    Model
    Date → Order Date (active) · Delivered Date → Delivered Date (active, separate table)

  4. Visual 1: Date[Month Name] + Total Sales.

    Your result should look like this:

    Month Total Sales (₹)
    Mar 500
    Apr 500

  5. Visual 2: Delivered Date[Month Name] + Total Sales.

    Your result should look like this:

    Month Total Sales (₹)
    Apr 800
    May 200

Show the result

Your result should look like this (fictional sample):

Order By order date By delivered date
901 (₹500) Mar (FY 2025-26) Apr (FY 2026-27)
902 (₹300) Apr Apr
903 (₹200) Apr May

Explain what happened

Each role table filters Orders through its own relationship, so the same order sits in March on one visual and April on the other.

Explore further

  • One-table alternative: Sales by Delivered = CALCULATE([Total Sales], USERELATIONSHIP('Date'[Date], Orders[Delivered Date])) with the delivered relationship inactive.

Detailed reference for this lesson:

One Date table can do two jobs: order date and delivered date. This is called a role-playing dimension (one dimension playing different roles).

You already have the inactive-relationship pattern in section 11.4, with USERELATIONSHIP. That is one date table playing two roles. The other pattern is a second physical date table, often named Delivered Date, so a page can show order month and delivery month together without a measure switch.

Steps – a second date role

  1. In Model view, use Modeling › New table and set Delivered Date = 'Date'. You get a copy of the columns. Mark Delivered Date as a date table on its own date column too, if you will use time intelligence on it.
  2. Relate Delivered Date[Date] to Orders[Delivered Date], single direction, many orders to one date. Leave Date[Date] to Orders[Order Date] as the active order-date path.
  3. Hide one of the two if a page should not offer both, or name them so a report author cannot mix them up: Order Date and Delivered Date.
  4. Build two visuals. One uses Date[Month Name]. The other uses Delivered Date[Month Name]. Same measure [Total Sales].

Worked example. A fictional order is placed in Pune on 31 March (IST) and delivered on 1 April. On the order-date visual it sits in March. On the delivered-date visual it sits in April. Both can be on one page because the two date tables are different roles. This copy costs a little memory. If you only need one axis at a time, keep a single Date table and USERELATIONSHIP (section 13.12) instead.

Ravindra Bagale's Tip

A common mistake is two active relationships from the same Date table to Order Date and Delivered Date. Power BI will turn one of them off. Either accept the inactive line and activate it inside CALCULATE, or copy the date table so each role has its own table. Don't mix both. Pay attention!

Fix common mistakes

Wrong result Reason Correction
The second relationship is dotted (inactive) Two relationships from the same Date table Copy the table, or use USERELATIONSHIP
Both visuals show the same numbers Visual 2 uses Date[Month Name] Use Delivered Date[Month Name]
Delivered YTD is wrong The copy is not marked as a date table Mark the copy too

Practise independently

Practice task

With one Date table and USERELATIONSHIP, what is Sales by Delivered for April? And Total Sales by order date for March?

Hints
  • 901 is delivered in April.
Solution

Sales by Delivered, April: ₹800 (901 ₹500 + 902 ₹300). By order date, March: ₹500 (901).

Think like a professional

Agree with finance which date drives revenue, and label visuals "by order date" or "by delivery date".

Loading DAX practice tasks...

  1. Units by order month (Easy): Shown per Month. Measure: Units Ordered = the existing quantity measure, unchanged.
  2. Units by delivered month (Easy): Shown per Month. Measure: Units Delivered = [Total Qty] using the DeliveredDate relationship.
  3. Orders by order year (Easy): Shown per Year. Measure: Orders Placed = the existing order count measure, unchanged.
  4. Orders by delivery year (Medium): Shown per Year. Measure: Orders Delivered = [Total Orders] by delivery date.
  5. Delivered sales in a quarter (Medium): Slicer: FY 2025-26 and FYQuarter Q2. Measure: Delivered Sales = [Total Sales] by delivery date.
  6. Delivered Blinkit sales (Medium): Shown per FY. Measure: Blinkit Delivered = [Total Sales] for Platform "Blinkit" by delivery date.
  7. Average delivery minutes by delivery quarter (Medium): Shown per FYQuarter. Measure: Avg Mins Delivered = average DeliveryMins by delivery date.
  8. Ordered minus delivered units (Hard): Shown per FY. Measure: Qty Gap = [Total Qty] by order date − [Total Qty] by delivery date.
  9. Delivered sales per Pune store (Hard): Slicer: Store City = Pune. Shown per FY. Measure: Delivered Sales = [Total Sales] by delivery date.
  10. Delivered units in Q4 (Hard): Slicer: FY 2024-25 and FYQuarter Q4. Measure: Units Delivered = [Total Qty] by delivery date.

See all Power BI practice tasks

Ravi Sir's Cards for this lesson

Role-playing dimensions. One table may need to filter a fact in two ways. Only one link can be active.Revision card

मराठी cards