Role playing dimensions in Power BI

The role-playing dimension is not a concept exclusively related to Power BI. It’s a universal data modeling technique that comes from Kimball’s methodology.

Simply said, it’s the situation when you use one same dimension for creating multiple relationships to your fact table. Typical usage of role-playing dimension concept is with Date dimension since in many cases, your fact table will contain multiple date fields. For example, in the betting industry, there are fields DateBetPlaced and DateBetProcessed, which don’t need to be identical (and in most cases are not). So, let’s say that the business request is to analyze data both on DateBetPlaced and DateBetProcessed.

Solution with multiple references of one dimension

The first solution will be to create two copies of exactly the same Date dimension and relate DateKey to DateBetPlaced in the first case, and to DateBetProcessed in the second case. Something like this:

As you see, each date field from the fact table relates to its own Date dimension. And we say that each of these references "play" its role in the model.

Of course, this model is not optimal, because we are basically making data redundant without a valid reason. Additionally, we would need separate filters for each reference of the Date dimension, in order to get valid results.

Optimizing the model

Instead of keeping multiple references of one same dimension, we can relate one single dimension multiple times to a fact table. This concept behaves differently in different tools (in SSAS multidimensional for example, you can define multiple active relationships between dimension and fact table, in case your data source view has proper foreign keys in place. But, that’s out of the scope of this post), so I will focus on Power BI.

Therefore, I will just delete the redundant Date dimension from my data model and simply connect DateKey from Date dimension to DateBetProcessed field in my fact table.

What happened here? Power BI created a relationship, but as you notice, this relationship is marked with a dotted line. That’s because Power BI allows only one active relationship between two tables and in my case, it’s between DateKey and DateBetPlaced. So, when I put that on the report canvas, I get something like this:

I can see the total number of bets per month, but since my active relationship is between DateKey and DateBetPlaced, I am seeing totals based on dates when the bet was placed!

What if I want to see, instead of how many bets were placed, how many bets were processed per month. Here comes in hand DAX function USERELATIONSHIP. This function enables us to define which relationship should be active for a specific calculation.

So, when I write the following measure:

Bets Processed = CALCULATE(
                    COUNT('Fact Bets'[BetID]),
                    USERELATIONSHIP(DimDate[DateKey],'Fact Bets'[DateBetProcessed])
)

I’m explicitly saying to Power BI: here, I don’t want you to use default active relationship (DateKey – DateBetPlaced). Instead, I want you to switch to using other relationship (DateKey – DateBetProcessed) and make this relationship active for this calculation only!

Here are the results:

As you may notice, lines are different, based on the relationship used in calculations.

Conclusion

Using this technique, we enabled our users to slice and dice data from different perspectives and gave them the flexibility to analyze figures based on multiple scenarios, keeping our data model tidy and not redundant.