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”

How to Disable Custom Visual in Power BI Desktop Model

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:

  1. Clicking the ellipsis button on the Visuals pane
  2. Clicking Remove a visual
  3. Selecting the custom visual
  4. Clicking the Remove button
Removing Custom Visuals From Power BI Desktop
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”
Enable Custom Vizuals
  • I used “KPIStatusWithHistory” custom visual in my sample model
Custom Vizuals
Continue reading “How to Disable Custom Visual in Power BI Desktop Model”