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.
New to Power BI? Quickly learn about Data Visualisation in Power BI here.
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:
- FactInternetSales
- DimDate
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
Pros:
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
Cons:
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
Great article! Thanks for explaining this concept. I am trying to do something similar, but I have little more complexity:
Find the Sales by Ship Date where the Order Date is 2018.
As you can see here, I am filtering another relationship. How would you do this?
Hi Divya,
Thanks for your comment.
Let’s unwrap your problem.
You have a Sales table with two or more relationships to the Date dimension on ShipDate and OrderDate.
Let’s say both OrderDate and ShipDate relationships are inactive.
To get the Sales Amount by ShipDate you have to activate the relationship between ‘Sales'[ShipDateKey] and ‘Date'[DateKey].
Let’s wrap the logic in a measure:
Shipped Sales Amount = CALCULATE( SUM( ‘Sales'[SalesAmount] )
, USERELATIONSHIP( ‘Sales'[ShipDateKey], ‘Date'[DateKey] ))
Now you want to see the Sales by Ship Date when Order Year a certain number.
You can reference the “Shipped Sales Amount” measure we created earlier, but, this time activate the relationship between ‘Sales'[OrderDateKey] and ‘Date'[DateKey].
The new measure will look like this:
Shipped Sales Amount by Order Date = CALCULATE( [Shipped Sales Amount]
, USERELATIONSHIP( ‘Sales'[OrderDateKey], ‘Date'[DateKey] ))
What you can do, you can hard code a filter on “Shipped Sales Amount by Order Date”, for instance 2018. In that case the above measure will look like this:
Shipped Sales Amount for Order Year 2018 = CALCULATE( [Shipped Sales Amount]
, USERELATIONSHIP( ‘Sales'[OrderDateKey], ‘Date'[DateKey] )
, ‘Date'[Calendar Year] = 2018
)
You can also add another Date dimension to the model, call it Order Date, create a relationship between Order Date and Sales, then it can be used on a slicer to dynamically filter the “Shipped Sales Amount by Order Date” measure. However it is not recommended unless you really need to have another Date table.
I encourage you to have a look at my other post around Role-Playing Dimensions in SSAS Tabular for more details.
Hope that helps.
Cheers