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. But, in this post I show you two new alternative ways to handle role playing dimensions without importing tables, for instance DimDate, into the Power BI model several times. 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 used AdventureWorksDW2016CTP3, 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
- Save the model
- Rename DimDate and FactInternetSales to make them more user friendly
- Create a new calculated table by clicking “New Table” button from “modelling” tab from the ribbon
- We are creating a copy of “Date” table using DAX expression. To do so just type the following DAX expression:
Order Date = All(‘Date’)
- As you might noticed the icon for “Order Date” table () is a bit different than the normal table icon () as the new table is a calculated table
- Create two more calculated tables for “Ship Date” and “Due Date” using the same expression
- Click “Relationships” to create new relations between the calculated tables we created and “Internet Sales” table
Note: You can keep the original Date table in the model if you need to have side-by-side role playing dimensions.
We are done now. We have all roles in our model. Keep reading if you’re eager to see some simple reports.
- Switch to “Report” view
- I want to create new measures to support side-by-side role playing dimensions
- Right click on “Internet Sales” table and click “New measure”
- Use the following DAX expressions to create a new measure for each “Sales by Order Date”, “Sales by Due Date” and “Sales by Ship Date”
Sales by Order Date = CALCULATE(SUM(‘Internet Sales'[Sales Amount]), USERELATIONSHIP(‘Internet Sales'[OrderDateKey],’Date'[DateKey] ) )
Sales by Due Date = CALCULATE(SUM(‘Internet Sales'[Sales Amount]), USERELATIONSHIP(‘Internet Sales'[DueDateKey],’Date'[DateKey] ) )
Sales by Ship Date = CALCULATE(SUM(‘Internet Sales'[Sales Amount]), USERELATIONSHIP(‘Internet Sales'[ShipDateKey],’Date'[DateKey] ) )
- Put a column chart on the report and put “Fiscal Year” column from “Order Date” to the chart axis then put “Sales Amount” from “Internet Sales” on values
- Repeat this for the other two date dimensions
- Now put a matrix on the report and put the new measures we created before on values then put “Fiscal Year” from the “Date” table on rows to have all role playing dimensions side-by-side
- Organise all charts and we are done
Role Playing Dimensions in DirectQuery Mode
Although the DirectQuery is an awesome feature in Power BI it has some few limitations. One of them is that we CANNOT create calculated tables. So we have to go for an alternative solution.
Note: You can learn more about DirectQuery here. But, some information is out-dated as in the new release of Power BI some of the limitations like creating calculated column and calculated measure are resolved. So the limitation on creating a calculated table in DirctQuery model might bet resolved in the next releases of Power BI. (Power BI Desktop current version: Version: 2.33.4337.281 64-bit (March 2016)).
Let’s get the job done in DirectQuery.
The first steps of getting data are the same just you need to select “DirecyQuery” in “Connection Settings” page instead of “Import”.
After loading the model follow the steps below:
- Click “Edit Queries” from “Home” tab from the ribbon
- Rename DimDate and FactInternetSales to user friendly names
- Right click on “Date” and select “Reference”
- If you want to see the Power Query “M” scripts behind the seen for a reference table click on “Advanced Editor” from “View” tab
- Rename the reference table to “Order Date”
- Repeat this and create Due Date and Ship Date reference tables
- Click “Close & Apply”
- Switch to “Relationships” view
- Keep just the correct relationships and remove unwanted ones between “Order Date”, “Due Date”, “Ship Date” and “Internet Sales”
Alright, we have all the role playing dimensions in our model.