Martin's Blog

Role-playing dimensions present some interesting challenges for modeling & reporting in Power BI (and SSAS Tabular for that matter), but also some opportunities to think outside the box and create more intuitive solutions.

What are role-playing dimensions?

In technical terms a role-playing dimension is when a dimension table has multiple (foreign key) relationships to the same fact table. In more non-technical terms, it is when you have the same attribute (“Date” for instance) that can relate to the same metrics in different ways.

If you look at the data model below as an example, you can see the Date entity could be used to reference either the Invoice Date or Delivery Date from the Sales entity, changing the perspective of the metrics we’re looking at.

With this model in place, all metrics from the Sales entity will use the Invoice Date (active relationship) to relate to any attribute in the Date entity. The inactive relationship (between Delivery Date from Sales and Date from the Date entity) is indicated by the dotted line.

The challenge: How do we allow end users to analyze the data from both an invoice and delivery date perspective?

Common Solutions

There are two well-known solutions to this challenge, and I’ll explain both briefly:

Multiple Date entities

With this option you create multiple (and almost identical) entities that will reference the different dates of your Sales entity. It works well if you have a small number of role-playing attributes or when the data model itself is pretty simple, but quickly becomes confusing to end users as they wouldn’t know which one to use (or not) when combining measures from other entities.

Multiple measures

Your data model will resemble that of the very first image at the top (one Date entity and multiple relationships) and the solution is to create different measures specific to the relationship that was used. It could look something like this…

Ext Price by Delivery Date = 
CALCULATE
(
    [Ext Price]
,   USERELATIONSHIP('Sales'[Delivery Date], 'Date'[Date])
)

This approach works well if you only have a few role-playing attributes, but it gets messy otherwise (in my opinion). Think of a scenario where you have many role-playing attributes (order date, delivery date, invoice date, etc.) and you have multiple measures (sales amount, unit price, invoice amount, tax amount, etc.). Expressing these in terms of the different dates will result in many measures for each role-playing attribute!

My Preferred Solution

I like to deal with role-playing dimensions in a slightly different manner, primarily because I feel that the other solutions are either too messy or not intuitive (user-friendly) enough. Here’s my preferred approach:

1. Create a filter dimension

This is what the end user will use to choose a date on which they’d like to “filter”. It isn’t necessarily a filter as you would expect from other filter-like attributes, but rather used as a “filter” to change the perspective of the data you’re looking at.

You can create the filter dimension with a DAX formula, or by manually capturing the data. In our model the Date Filter dimension (entity) will consist of the following values: “Delivery Date“; “Invoice Date”.

2. Create a measure that’s “aware” of the filter dimension value

Ext Price (Filter Aware) =
VAR 
    SelectedDate = SELECTEDVALUE
                   (
                       'Date Filter'[Date To Filter]
                   ,   "Invoice Date"
                   )
VAR 
    UseInvoiceDate = CALCULATE
                     (
                         [Ext Price]
                     ,   USERELATIONSHIP('Sales'[Invoice Date], 'Date'[Date])
                     )
VAR 
    UseDeliveryDate = CALCULATE
                      (
                          [Ext Price]
                      ,   USERELATIONSHIP('Sales'[Delivery Date], 'Date'[Date])
                      )
 
RETURN 
    SWITCH 
    (
         SelectedDate 
    ,    "Invoice Date"
    ,    UseInvoiceDate 
    ,    "Delivery Date"
    ,    UseDeliveryDate 
    ,    UseInvoiceDate 
    )

The formula looks more complicated than it is: In the first variable declaration, we’re getting the selected value from our filter dimension, using “Invoice Date” as the default if the user hasn’t selected anything.

The UseInvoiceDate and UseDeliveryDate variables perform the work of activating the necessary relationship in our model, and finally the SWITCH function is used to return the measure value based on the selection in our filter dimension.

With this approach the end users have the option to select which date they want to use, I have less measures to maintain and I can easily explain that Invoice Date will be used as the default.

Want to see it in action? Download the source code from my GitHub repo.