Role-playing dimension is one those concepts that is discussed a lot from time to time. I also posted an article about implementing role-playing dimensions in Tabular models.
To recap, in the role playing dimensions in SSAS Tabular article I explained three different solutions:
- Importing role playing dimensions several times into the model
- Creating database views in the source side (in case your source is a from of RDBMS like SQL Server, Oracle etc…) then import the data into the model
- Keep the inactive relationships in the model and create several measures to take care of different roles using USERELATIONSHIP functions in DAX
I this post I explain implementation of the third option above. In this scenario you need to create especial calculated measures based on the roles you have in a fact table. One the most common role-playing dimensions is Date dimension. Consider you have to show Internet Sales Amount by Order Date, Due Date and Ship Date in a single chart in your report. In this case, having 3 different date tables won’t help us to achieve the goal.
Defining new Measures in Power BI Desktop
Basically, what I’m going to explain in this post is using inactive relationships between FactInternetSales table and the DimDate dimension by adding a new Calculated measure. In this case, we’ll be able to show Sales Amount by different roles, well, dates in this sample in a single chart.
As a sample you just need to import the following tables from AdventureWorksDW2012:
As I’m using one on of my previous samples that I’ve done some tidying up, but, you don’t have to do that. I also have some more tables, but, again, to experiment this sample all you need is to import FactInternetSales and DimDate tables into Power BI Desktop.
Now I need to create a new calculated measure:
- Go to Data view
- Right click on Internet Sales and click “New Measure”
- You can define a new calculated measure by clicking on “New Measure” from the ribbon
- Type the following DAX command then enter
Sales by Due Date = CALCULATE(SUM(‘Internet Sales'[Sales Amount]), USERELATIONSHIP(‘Internet Sales'[DueDateKey],’Date'[DateKey] ) )
- Repeat the previous step for Order Date and Ship Date as well to create two other calculated measures in the FactInternetSales table. Use the following DAX formulas:
Sales by Order Date = CALCULATE(SUM(‘Internet Sales'[Sales Amount]), USERELATIONSHIP(‘Internet Sales'[OrderDateKey],’Date'[DateKey] ) )
Sales by Ship Date = CALCULATE(SUM(‘Internet Sales'[Sales Amount]), USERELATIONSHIP(‘Internet Sales'[ShipDateKey],’Date'[DateKey] ) )
Now scroll down the “Internet Sales” tables you can see all the new measures successfully created.
Now we can easily create a report which have all of those calculated measures side-by-side.
- Go to Report view then add a Matrix to the report
- Expand “Internet Sales”
- Tick all calculated measures
- Expand “Date” and tick CalendarYear
- As CalendarYear is integer it automatically goes to Values and you need to move it to Rows
Now we have all different Sales Amount by Ship Date, Order Date and Due Date.
As you see I used a combination of two DAX functions to meet our sample’s requirements. For better understanding the whole formula, you need to understand CALCULATE function first. Without a doubt, CALCULATE is one of the most common functions used in every project involves with DAX formulas. If your project is involved with one of the following you’ll most likely to need CALCULATE function:
- SQL Server Analysis Services Tabular Model
- Power Pivot
- Power BI
I don’t want to explain how CALCULATE function works as it is out of scope so I leave it to you to search on the web and see how CALCULATE functions actually works.
Generally speaking of “USERELATIONSHIP” function, you should use it in a function like CALCULATE that takes a filter. The “USERELATIONSHIP” itself does NOT return any values. It actually enables a relationship to get used during a calculation.
If you already read my other blog post about Role Playing Dimensions you may ask yourself, “we do have all the roles side-by-side, so do we really need to import role-playing-dimensions as separate tables in our Power BI model?
A short answer is: Well, it depends! As always, it really depends on your case. This method has its own pros and cons. Let’s have a look.
Pros and Cons
1- You can use all the roles side-by-side in a same chart as you literally have a separate measure for each role
2- You are not importing several copies of the roles, for instance, you have just one Date dimension that can be used to slice and dice all of related measures across the whole model
3- It is more efficient in terms of storage and memory consumption
4- Your model is much more tidy when you don’t have several roles all over the model
1- In large models with lots of different roles, creating lots of measures to support different roles would be time consuming and also a bit hard to maintain
2- The measure names are getting long
3- Having lots of different measures that look very similar can be a bit confusing for the end user