Tag: Power BI DirectQuery

DAX, Power BI, Power BI Desktop

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.

To recap, in the role playing dimensions in SSAS Tabular article I explained three different solutions:

  1. Importing role playing dimensions several times into the model
  2. 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
  3. 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

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

Continue reading “Role Playing Dimensions in Power BI” →

Business Intelligence - BI, DAX, Power BI, Power BI Desktop, Power BI Desktop RS, Self-Service BI

How to Define A Measure Table in Power BI Desktop

In this post I show you a simple trick to make your Power BI model more organised and more readable. I call it creating a “Measure Table”. Let me explain. The story is that I was working on a model with lots of tables. The database schema was NOT a proper star schema so there were a bunch of measures spread into lots of different tables. On top of that we’ve created lots of calculated measures with different home tables which made it really hard to find a particular measure or calculated measure. I thought, well, when it is that hard to find the calculated measures at development time how hard it could be for a customer to find, understand and use the measures we created. The visibility of the calculated measures could be an issue when we have lots of measures in lots of different tables. You will soon feel the issue in customer training sessions when you need to navigate between lots of different tables to find a calculated measure.

Consider you create a Power BI model with direct connect to a SSAS Multidimensional instance. You will immediately notice that all measure groups have a special calculation icon (Measure Group Icon in Power BI) rather than a normal table icon (Table icon in Power BI) 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.

Measure Groups in SSAS Multidimensional Dirct Connect

I know, we can search and find the measures very easily, but, our model would be more organised and more user friendly if we can put all measures in one or more tables which contain just related calculated measures and nothing else. For instance, we can create a measure table for time intelligence calculations and name it “Sales Time Intelligence Measures” and put all  calculated measures like “Sales YTD”, “Sales LYTD”, “Sales Period Over Period” on it. It will make your model nice and clean, easy to use and easy to learn for your customers. It will also help you to train your customers more easily.

In this article I’ll connect to a SQL Server instance and will use the famous Adventure Works database. I also show you how to get the job done in both “Import” and “DirectQuery” modes as there are some limitations applied to the DirectQuery mode which makes it harder to do what we want.

Lets start.

Continue reading “How to Define A Measure Table in Power BI Desktop” →