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. In one my recent posts I used the same concept to implement role-playing dimensions in Power BI. So in this post I’m not going to explain role-playing dimensions in Power BI again.
In some cases you need to have 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. Again I refer to my previous post where I said I keep the original Date table in the model for a reason.
Defining a new Calculated Measure 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
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.
Now that we have all the roles side-by-side, do we really need to have role-playing-dimensions in our Power BI model?
A short answer is: Well, it depends! As always, it really depends on your case. But, I can say that role playing dimensions are reusable. So when you design role-playing dimensions in your model you can take advantage of of using them several times for different cases. But, using “USERELATIONSHIP” you need to implement it separately for each use case.
The other point is that role-playing dimensions is they are much cheaper to maintain and also much easier to change.
But, on the other hand, you cannot have all the roles side-by-side. Therefore, I prefer to consider both approaches in my models to be able to cover different use cases.