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

Continue reading “Role Playing Dimensions in Power BI” →

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

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

Update Sep 2022: I wrote this blogpost in Mar 2016. Power BI Desktop improved a lot since then. Currently, Power BI Administrators can Enable or Disable custom visuals for the reports published to Power BI Service from Power BI Admin Portal across the organisation or to certain security groups. There are also some settings for system administrators to Enable or Disable custom visuals on the user’s Power BI Desktop across the organisation via group policy settings. The users can also remove custom visuals from Power BI Desktop by following these steps:

Clicking the ellipsis button on the Visuals pane
Clicking Remove a visual
Selecting the custom visual
Clicking the Remove button

Removing Custom Visuals From Power BI Desktop

Disclaimer: The method described in this post includes modifying Power BI file (PBIX) outside of Power BI Desktop. It is highly recommended to take a backup of your PBIX file beforehand. It’s highly probable that you corrupt your PBIX file if make a mistake when following the method described here. So please follow the process on YOUR OWN RISK!

Custom visuals are awesome. It’s easy to import them to Power BI Desktop model and start using them. But, what if you decide to remove them from your model? Is there a way to disable an imported custom visual?

Well, the answer is No and Yes! I mean, NO, there is no specific setting or option you can manage imported custom visuals in Power BI Desktop. But, YES, there is a way you can get rid of an existing custom visual. In this article I show you how to do the job.

First of all, I’d like to inform you that Microsoft will add the feature to disable custom visuals in Power BI Desktop, but, until then you can follow the my trick to completely disable/remove a custom visual from your Power BI Desktop model.

As you might already know a PBIX file is a compressed file indeed, so you can open it with a ZIP editor software like 7-Zip.

Requirements:

Download and install 7-Zip. It’s a free open source file archiver/compressor
Download and install Notepad++ which is also free and open source. It’s an awesome text editor

Removing/disabling Custom Visual

Open you Power BI Desktop model (PBIX file) containing a custom visual
As you see you need to enable custom visuals, click “Enable custom visuals”

I used “KPIStatusWithHistory” custom visual in my sample model

Continue reading →

In the previous post I announced that I will speak in “Visualising Your Azure SQL Data Warehouse with Power BI” webinar on 23 Jan 2016. The webinar host was Pass Business Intelligence Virtual Chapter. It was such an amazing experience for me to speak in the webinar and I would like to thank all 105 attendees. The attendees showed their enthusiasm by asking lots of questions during the webinar.

In this webinar I demonstrated:

How to install Azure SQL DW in Azure Portal
How to configure firewall settings from Azure Portal and SQL Server Management Studio (SSMS) 2016
How to connect directly from Azure SQL DW to Power BI Service and the other way around
How to visualise you Azure SQL DW data warehouse data with Power BI Desktop (both Data Import and DirectQuery scenarios)
Comparing the features of different scenarios that helps you finding the best for your use cases

and much more…

You can see and download the session materials as follows.

Session Materials

Watch Visualising Your Azure SQL Data Warehouse with Power BI on YouTube

Continue reading “Webinar Materials: Visualising Azure SQL DW with Power BI” →

Tag: Power BI Desktop

Role Playing Dimensions in Power BI

Role Playing Dimensions in Import Mode

How to Define A Measure Table in Power BI Desktop

How to Disable Custom Visual in Power BI Desktop Model

Requirements:

Removing/disabling Custom Visual

Webinar Materials: Visualising Azure SQL DW with Power BI

Session Materials

Role Playing Dimensions in Import Mode

Share this:

Share this:

Requirements:

Removing/disabling Custom Visual

Share this:

Session Materials

Share this: