A few days ago, I started the trend #MonthOfDAXProblems on LinkedIn and received great feedback. My posts garnered comments with many ideas from the community.

Today we are going to look at a scenario where there are multiple date tables defined, each for Ship Date, Due Date, Delivered Date etc. – an extremely common problem but very easily mitigated if things are set up properly.

So how do we handle something like this? By using a Role Playing Dimension magic.

Role Playing Dimension is used when we have more than one Business Dates for analysis.

What is a Role playing Dimension? A table with multiple valid relationships between itself and another table. This is most commonly seen in dimensions such as Time, Customer, Address etc.

We will use Date as our example as its more common:

In simplest of Sales – we see Order Date, Ship Date, Delivery Date, Due Date and so on.
Does it mean, when we evaluate Sales by these different Dates, we need to create different Date tables?

No. We create one Date which will have one active relationship with Sales on Datekey of your choice but can have other inactive relationships between other Dates.
Now to evaluate any expression by all these different dates:

1. First, we delete any duplicate Date tables (or similar) if they exists from the model.

2. Then we introduce only one Date Dimension and create Active and Inactive relationships between Datekeys.

3. And then use DAX Function USERELATIONSHIP() to activate needed relationship for the current evaluation. Like to get Sales amount by different dates, we activate Date[DateKey] and Sales[ShipDateKey] for Shipping, Date[DateKey] and Sales[DeliveredDateKey] for Delivered.