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