In this post I want to explain how to handle role playing dimensions in Power BI. I wrote an article awhile ago regarding role playing dimensions in SSAS Tabular which is valid for Power BI Desktop.
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
In this post I show you alternative ways for the first two solutions to handle role playing dimensions without importing data several times into the Power BI model. You also don’t have to create database views on your source database. I show you how to manage this in both DirectQuery and Import modes when connecting Power BI Desktop to a SQL Server database. I explain the third option in another post.
I used AdventureWorksDW, but, you can use any other versions of AdventureWorksDW database or you can mimic the process to your own model.
Note: If you are designing a star schema for your data warehouse you can easily create a Date dimension as explained here.
The idea is to manage role playing dimensions in Power BI Desktop itself in the easiest way possible.
Role Playing Dimensions in Import Mode
- Open Power BI Desktop
- Get data
- Select “SQL Server”
- Enter the server and database names then click OK
- Select DimDate and FactInternetSales from the list then click “Load”
- “Import” mode is selected by default. Click OK
) rather than a normal table icon (
) which makes the measure groups more recognisable for the end users. For instance, you can easily find any calculated measure related to “Internet Sales” under the “Internet Sales” measure group.
