I would like to show you two common ways of dealing with multiple date dimensions at once in your Power BI model. These methods are also applicable to other role-playing dimensions, and not only dates. The first approach will use multiple relationships to a single date dimension, whereas the second approach will make the use of several date dimensions.
CREDIT GOES TO: https://sqlitybi.com/
Approach #1 – Multiple Relationships to a Single Date Dimension
The first approach makes use of multiple relationships to a single date table. One relationship is active, whereas the rest are inactive. To be able to make use of all the relationships, you would have to create several measures to reference each of the date relationships separately.
As you can see in the above illustration, there is a single date dimension with multiple relationships connected, namely: Due Date, Order Date, and Ship Date. To allow the measures to work across all relationships, you have to make use of the USERELATIONSHIP DAX function. The function specifies the relationship to be used. For example, in the below DAX script, the relationship should use the OrderDateKey path.
Advantages
With this approach, you have several advantages, such as:
- Side by side measures using a single date attribute since you already have a measure for each date relationship. For example, let’s say you would like to compare the Sales Amount for each date by Year in a single table.
- Having a single date dimension can make it easier for users to use the model. For example, it requires only a single slicer and not several slicers date slicers.
Disadvantages
On the downside, this approach also has several drawbacks, namely:
- Multiple measures have to be created to support all the different date roles. For example, let’s say you have a date table with three date roles and 20 measures in the fact table. That would mean if you want to use all three roles along with all 20 measures, you will end up with 60 measures. You can multiply your number of measures several times due to this approach. In the end, your model can get very complicated for users.
- Because several roles require several measures, it is also natural to conclude that supporting and maintaining these measures will be harder.
- Measure names can get very long and feel redundant. For example, instead of having a measure named Total Sales, you have Total Sales by Due Date. If you add another role-playing dimension to the mix, for example, Address, then you got Total Sales by Due Date by Shipping Address.
- The DAX function USERELATIONSHIP does not support row-level security.
Approach #2 – Multiple Date Dimensions
The second method makes use of multiple date tables instead of using a single date table. Therefore, a unique relationship can be established to each date table without the need for using additional DAX functions to indicate the relationship to use.
As you can see in the above illustration, there are three date dimensions, each with a unique relationship, namely: DueDate, ShipDate, and OrderDate. You don’t need to import the date dimension multiple times. Instead, you can duplicate it in memory. As an example, you can use the below DAX statement:
Advantages
Using this approach, you have several advantages, namely:
- Having multiple date dimensions reduces the need to use the USERELATIONSHIP function. Therefore, your measures will be easier to maintain, names are shorter, and finally, measure redundancy and duplication is less. As you can see image below, a single measure is used “Sales Amount,” instead of three.
- Even if you add additional role-playing dimensions such as Address, you still end up with a single Sales Amount measure.
Disadvantages
Using this approach also has several problems, such as:
- Instead of having a single date dimension to interact with, users need to switch between three date dimensions, which could be confusing.
- Side by side measures using a single date attribute is not possible. You will not be able to compare the Sales Amount using a single date attribute in a single table.
- Several date slicers have to be used to slice among all three date dimensions.
Do you have any other approaches to solve the role-playing dimension problem? Please post it in the comments below.
Bonus Approach #3 – Calculation Groups
The third approach is by no means a 3rd approach but an extension to the first two approaches. We will apply Calculation Groups on the first two approaches, whose primary purpose is to significantly reduce the number of measures used inside reports. Using Calculation Groups, you can set up your date relationships similar to either approach #1 or approach #2.
Calculation Groups on Approach #1
Consider the below model diagram, where we have two fact tables, FactProductInvetory and FactInternetSales. Both fact tables have a relationship to DimDate, whereas FactInternetSales multiple active/inactive relationships. Lastly, the Calculation Group is called Time Intelligence, which will reduce the fact table measures.
If we compare the Fields view, we can see that we indeed have fewer measures since they are now included in the Calculation Group.
On the other hand, if you look inside the Calculation Group, we will calculate three measures according to the three specified dates.
This simple example gives a total measure reduction of 30% from 10 to 7 measures. The benefit of this is that you only need to create the Calculation Group measures once, and it will automatically work for any additional measures added to the model. You can further extend the Calculation Group with standard Time Intelligence measures such as QTD, YTD, MTD, PY, etc.
Calculation Groups on Approach #2
This method uses the same multiple date relationships to individual date tables.
If you need to apply Calculation Group on the above date dimensional setup, you would need to create a Calculation Group for each date table. That means duplication of measures for each Calculation Group. Still, the number of measures will be massively reduced using the calculation groups. Besides, duplicating a group of measures is easy by using a tool such as Tabular Editor.
Disadvantages of Calculation Groups
First of all, there are plenty of benefits to using Calculation Groups. However, it also has its limitations. I won’t cover all of them since there are plenty. Instead, I will highlight a few examples.
Mixed role-playing measures
Suppose you needed to compare two measures side by side, using two different dimensions. In that case, you could not do this using Calculation Groups because it does not allow you to select individual measures since it only allows you to choose groups based on a single dimension.
Considering the below example, let’s say I need to show side by side the Sales Amount by Due Date, along with the Total Price by Ship Date. You wouldn’t be able to select them side by side unless you use the measure without a Calculation Group.
You can see that you can filter by an individual dimensional grouping in the below illustration but without any combinations between them. This issue may be more elaborate if you’d have other role-playing-like dimensions such as Employee. Where you’d like to group an employee by the following roleplaying attributes SubmittedBy, ReceivedBy, ResolvedBy, or ProcessedBy.
Misleading measures
If you go with the first approach using a single date dimension, you might end up using mixed measures from different fact tables in a single visual. When you use different facts and a Calculation Group, the group name bucket could be misleading for self-reporting users. Consider the below diagram where I would like to view Sales, Total Price, and Total Units on a single visual sliced by date.
After creating the visual, you end up with all the measures under the same Calculation Group name. This situation can cause additional confusion as the Total Units is the Movement Date and not the Order Date. Keep in mind that this is only a simple example, but it can prove a challenge with more advanced measures and relationships. In the end, you might end up creating solo measures for Total Price and Sales to avoid Calculation Groups.
Explicit conditional formatting
If you have created a Calculation Group with a collection of measures, you cannot create conditional formatting for a single Calculation Item. Any conditional formatting applied with be applied across all Calculation Items inside a Calculation Group. In the below example, I have added Sales Amount and used conditional formatting as Data Bars. As you may see, the formatting is applied to all columns. That doesn’t give any flexibility if I wanted to create different formatting for Same Period Last Year (SPLY). In conclusion, you also end up splitting the measures to achieve singled-out conditional formatting. You can never be prepared enough for what the business user wants to see.
Hopefully, that extends the topic a little by adding some flavor of Calculation Groups. Therefore, is still much that could be shown in regards to Calculation Groups. But I am afraid that would have to be another post by itself.
