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.