Role-Playing Dimensions

Power BI · Data Modeling

Theory

A role-playing dimension is one dimension table used for more than one purpose. The classic case is Date: a Sales table might have both OrderDate and ShipDate, and both should be analysed by the same calendar. Power BI allows only one active relationship between two tables, so you create 'Date'[Date] → Sales[OrderDate] as active and 'Date'[Date] → Sales[ShipDate] as inactive (shown as a dashed line).

Measures use the active relationship automatically. To analyse by ship date, write a measure that activates the inactive one with USERELATIONSHIP inside CALCULATE. For example, suppose our Sales table also had a ShipDate column; order 103 was placed on 20 Feb but shipped on 2 Mar. By order date, February has ₹2,300 of sales; by ship date February has only ₹800 and March rises to ₹3,700. The alternative is to load a second copy of the Date table (Ship Date) with its own active relationship.

Why we use it

Businesses ask the same question through different dates: what was ordered versus shipped versus delivered. Role-playing dimensions answer all of them from one calendar without duplicating fact data.

When to use it

Use inactive relationships plus USERELATIONSHIP when you need a few measures by the second date. Use a separate copy of the dimension when users need to slice by both dates independently in the same visual or with slicers.

How it works

  1. Fact table has two date columns
  2. Create the active relationship on OrderDate
  3. Create an inactive relationship on ShipDate
  4. Must users slice by both dates independently? Yes: Add a second copy of the Date table. No: Write USERELATIONSHIP measures.
  5. Analyse by order date and ship date

Syntax

Model: 'Date'[Date] → Sales[OrderDate] (active)
       'Date'[Date] → Sales[ShipDate] (inactive)
DAX:
USERELATIONSHIP(<columnName1>, <columnName2>)  // only inside CALCULATE / CALCULATETABLE

Example

// Assume Sales also has a ShipDate column
Sales by Ship Date = 
CALCULATE(
    [Total Sales],
    USERELATIONSHIP('Date'[Date], Sales[ShipDate])
)
// Visual: 'Date'[Month] with [Total Sales] and [Sales by Ship Date]

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic