Power BI role-playing dimensions: one date table, several roles
Power BI role-playing dimensions are dimension tables that filter the same facts in more than one way. A date table is the usual example, because a sale has an order date, a ship date and a delivery date. Power BI allows only one active relationship between two tables, so the design takes thought. This guide explains both documented approaches and when to use each.
- One table per role
- Inactive relationships
- USERELATIONSHIP measures
Last updated
What are Power BI role-playing dimensions?
Microsoft defines it as a table that can filter the same facts in different ways. In its example, the date table has three relationships to reseller sales. The same table can filter the facts by order date, ship date or delivery date.
A data warehouse handles this with a single date table. At query time, the join column decides the role. A Power BI model can copy that idea. You add more than one link between the two tables.
There is a catch, however. Only one relationship between two model tables can be active. All the others must be inactive. So the active one sets the default filter, which is usually the most common date in reports.
Three limits of a single shared dimension table
Microsoft lists these drawbacks of relying on inactive relationships for every extra role.
More measures
Each inactive role needs its own measures with USERELATIONSHIP. That work is tedious, and it clutters the Data pane.
No simple summaries
A report author who sums a column cannot use an inactive link. They must write a measure first.
No combined filters
With one active path, a visual cannot filter the facts by different date roles at the same time.
Build one table per role in four steps
These steps follow the method in Microsoft's guidance.
Remove inactive links
First remove each inactive link between the two tables.
Rename the original
Next rename the first table, so that its name shows the role it keeps.
Copy the table
Then create a copy for the other role. For an Import table, Microsoft recommends a calculated table.
Relate and rename
Finally add an active link for the new table. Then rename its columns to show the role.
Why use separate tables for Power BI role-playing dimensions?
Active relationships widen how a model can be used. Microsoft recommends defining them whenever possible. In its airline example, reports need to filter by departure airport and group by arrival airport at the same time. That requires two active relationships, and therefore two airport tables.
Security is a further reason. Row-level security filters propagate only through active relationships. They never propagate through inactive ones, even when a measure uses USERELATIONSHIP. So a model with security roles benefits from duplicated dimension tables.
The cost is modest. An extra table adds to the model size and to the refresh time of an Import model. However, such tables hold few rows next to a fact table. So Microsoft notes that this is rarely a concern.
When do inactive relationships still make sense?
Microsoft describes a case where the single table works well. A sales table has an order date and a ship date. Nearly every measure filters by order date, and only one measure needs the ship date.
In that case, the relationship to the order date stays active. The one special measure then uses the USERELATIONSHIP function inside CALCULATE. That function turns on the other link, but only while the measure runs.
Two conditions apply, according to the guidance. First, report visuals have no need to filter by the different roles at the same time. Second, you use USERELATIONSHIP for the relevant calculations. When both hold, inactive relationships keep the model small.
Naming habits for tables that play a role
Clear names matter here, because several tables with the same columns confuse report authors quickly.
- Name each table after its role, such as Ship Date
- Prefix column names with the role, such as Ship Year
- Add a table description that states what it filters
- Keep one relationship per role table, and keep it active
- Use a calculated table or a referencing query for the copy
- Check slicers for a blank entry from missing dates
Power BI role-playing dimensions: common questions
How do I create Power BI role-playing dimensions for dates?
Microsoft describes a common technique: create a dimension table for each role. You can build each one as a referencing query in Power Query or as a calculated table in DAX. The model then holds, for example, a Date table and a Ship Date table, each with one active relationship.
Do Power BI role-playing dimensions make the model much larger?
Usually not by much. Microsoft calls the duplication a minor price, because dimension tables typically store far fewer rows than fact tables. The model does grow, and an Import refresh takes a little longer. For most models, however, the clearer design is worth that cost.
What does USERELATIONSHIP do?
It is a DAX function that turns on an inactive link for one measure. You use it inside CALCULATE and name the two columns that the link joins. The filter then flows through that link while the measure runs. After that, the model goes back to its active link for every other measure.
Does row-level security work with role-playing dimensions in Power BI?
Only through active relationships. Microsoft states that row-level security filters propagate only through active relationships. They never propagate through inactive ones, even when a measure uses USERELATIONSHIP. So if a security rule sits on a role-playing table, give each role its own table with an active relationship.
Can Power BI Agency restructure our date tables?
Yes. One of our consultants reviews how your reports filter by each date, then chooses between separate role tables and inactive relationships for each case. We work online in your own Microsoft tenant, with the scope fixed in a written quote. Power BI Agency is a division of Sataware Technologies, a Microsoft Solutions Partner, and is not part of Microsoft.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.