Role Playing Dimensions in Power BI

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

Power BI SQL Server Connection

  • Select DimDate and FactInternetSales from the list then click “Load”
  • “Import” mode is selected by default. Click OK

Power BI Connection Settings

  • Save the model

Power BI Desktop

  • Rename DimDate and FactInternetSales to make them more user friendly

Power BI Desktop Task Pane

  • Create a new calculated table by clicking “New Table” button from “modelling” tab from the ribbon

Power BI Desktop Calculated Table

  • 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 (Power BI Desktop Calculated Table Icon) is a bit different than the normal table icon (Power BI Desktop 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

Power BI Desktop Role Playing Dimension

  • Click “Relationships” to create new relations between the calculated tables we created and “Internet Sales” table

Power BI Desktop Relationships View

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.

Power BI Desktop Create 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] ) )

Power BI Desktop New Measures Added

  • 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

Power BI Desktop ColumnChart

  • Repeat this for the other two date dimensions

Power BI Desktop ColumnCharts

  • 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

Power BI Desktop Side-by-side Role Playing Dimension

  • Organise all charts and we are done

Power BI Desktop Report

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)).

Power BI Desktop Create Calculated Table Disable in DierectQuery

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”.

Power BI Desktop Connection Settings DirectQuery

After loading the model follow the steps below:

  • Click “Edit Queries” from “Home” tab from the ribbon

Power BI Desktop Edit Queries

  • Rename DimDate and FactInternetSales to user friendly names

Power BI Desktop Query Editor

  • Right click on “Date” and select “Reference”

Power BI Desktop Reference Table

  • If you want to see the Power Query “M” scripts behind the seen for a reference table click on “Advanced Editor” from “View” tab

Power BI Desktop Advanced Editor

  • Rename the reference table to “Order Date”
  • Repeat this and create Due Date and Ship Date reference tables
  • Click “Close & Apply”

Power BI Desktop Query Editor 2

Power BI Desktop Query Changes

  • Switch to “Relationships” view

Power BI Desktop Relationships 2

  • Keep just the correct relationships and remove unwanted ones between “Order Date”, “Due Date”, “Ship Date” and “Internet Sales”

Power BI Desktop Relationships 3

Alright, we have all the role playing dimensions in our model.

All done!

Leave a Reply