Analyse Data by using Role-Playing Dimensions Effectively in Power BI

Role-playing dimensions are one of the key concepts in Data Warehousing and Business Intelligence development. If you are not familiar with it you can read James Serra's Blog article on Role-Playing Dimensions from here

In this article, I'm going to demonstrate to you How to Analyse Measures by using Role-Playing Dimensions Effective manner. If you are familiar with Relationships in Power BI you know by now, you cannot create two active relationships simultaneously with the same tables in a Power BI model. You must make one relationship Active while other relationship's inactive. 

For this Demo, I've taken the WideWorldImportersDW demo database. So you can download it from here for free. For simplicity, I only have taken the Sales and Date table and imported it into Power BI. 

You can see in this data model we have a Sales transaction fact table and Date dimension table. If you looked closely the Sales table contains two Date columns, Invoice Date and Delivery Date. But, in theory, we cannot have both relationships as active. This means only one relationship filter can apply in your report. In this case, I can only visualize the Sales data using the Invoice Date. Because it is the active relationship in this model. Even the relationship exists for the Delivery Date, since it is inactive we cannot use it for visualization. 

So, in this model, Date table working as a role-playing dimension. At a time it plays the Invoice Date role. In another time the role of the Delivery Date. 

In this model, I created a Calculated Measure for Quantity and named it as Invoiced Qty. Because, in the model, we have made Invoice Date as Active relationship. 

Invoiced Qty = SUM(Sale[Quantity])

At any given time the end-user can visualize the Quantity with Invoice Date as below. 

Then Why is an Inactive Relationship?

Though you cannot use inactive relationship filters straight away, you can use them Programmatically. This means Power BI allows us to create Calculations by making use of them. I'm going to create another Measure called Delivered Qty by referring the relationship Sales[Delivery Date] ---- Date[Date]. 

Delivered Qty = CALCULATE([Invoiced Qty], USERELATIONSHIP(Sale[Delivery Date Key], 'Date'[Date]) )

Let's visualize them both and see what are the differences. By looking at both visuals, at a glance you would not see much difference. But, in fact, there are references in figures. Because both measures calculating the Item Qty respective to Date.  

If you put both Measures and visualize, you may see a slight difference like this. 

Let's Improve the End-user Experience 

Now, we know how to use role-playing dimensions by creating DAX measures. What if we can make this more usable. How about a Slicer? If we can create a slicer or a Switch to toggle between and each role, this will be an awesome experience for the end-user. Here is how to do it. 

1. Create a Table by Entering Roles (Filter values) 

In this step, use the Enter Data feature to key the values you need to be a toggle in the visual. I'll make this table hide for report users. 

 

2. Create a calculated Measure to get the Selected filter value. I did not want to make my final Measure too complex. So I break it into small pieces. This is to make it more clear. You know what I mean 😉

Selected Role = SELECTEDVALUE('Date Role'[Analyzed By]) 

  

3. The final step, create a Calculated Measure for Quantity using the Selected Role measure we created in step 2. We need to show the Quantity value, either Invoiced Qty or Delivered Qty based on the selection. 

 

Total Qty = IF([Selected Role] = "Invoiced", [Invoiced Qty], [Delivered Qty])

Finally, you can give a rich analysis experience to the end-user by combining features offered by Power BI. 

Hope you learn how to analyze data with role-playing dimensions simultaniously. You can download the Power BI Demo pbix file from here.