Measure Dependencies in Power BI

Update:  Unfortunately after I released the latest version of my workbook in this article, something changed with Excel to stop the file working as designed.  At this stage I do not know what the issue is, but it seems to only affect the current channel versions of Excel.  I have just reinstalled the deferred channel of Excel and it works as designed.  Read on to see what this workbook is “supposed” to do, but be aware it may not work if you have the current channel version of Excel.  I will update when it is working across all versions.

I was chatting to Jeff Weir a couple of weeks ago discussing measure dependencies in Power Pivot.  Jeff alerted me to the existence of some DMVs (special queries to extract cool stuff from data models) that can be used to extract a list of measures with all of the dependent measures too.  This got me thinking and I decided to update my local host workbook so that it can access and display measure dependencies easily from my Power BI Desktop workbooks.  You can see it in action in the image below.

Just click on a measure row in the list, and then decide if  you want to see which measures refer to the selected measure (dependants), or which measures are needed as inputs the selected measure (precedents).

 

How it Works

When you open an instance of Power BI Desktop on your PC, Power BI Desktop actually runs an instance of SQL Server Analysis Services Tabular as a service hidden in the background on your PC. It is possible to find this instance of SSAS Tabular and directly connect to it (as a server) using Excel.  This is what my Excel workbook does.  Note that you must have 1 and only 1 copy of Power BI Desktop running on your PC.

Local Host Workbook Features

This is now the third update of my Local Host Workbook (originally released here and then updated here).

Connect a Pivot Table

You can connect a Pivot Table in the workbook to the data model in Power BI Desktop.  It then works just like Analyze in Excel however it is connected to Power BI Desktop and not PowerBI.com.

image

Note you will be prompted to approve access to the database.  This will happen each time you close and reopen the Power BI Desktop file.

The reason this happens is Power BI Desktop creates a new run time instance of SSAS Tabular each time you start a new session, and each session has a random database name and a random port number.

Memory Usage

Displays memory usage in your data model so you can see what tables and columns are chewing up the space in your workbook.

image

Measure List and Dependencies

You can automatically extract a list of all the measures in your Power BI Desktop file into a table in Excel.  The first time you connect you will need to refresh the measures list (shown as 1 below). Note: you will also need to refresh each time you make changes to measures in Power BI Desktop and each time you close and then reopen Power BI Desktop.

After refreshing, you will see a list of all measures in the workbook along with other useful information including:

  • the number of times the measure as been referenced (number of dependants)
  • the number of other measure references needed as inputs to create this measure (precedents).

From this list, you can click on one of the measures (shown as 2 above) and filter the list using one of the filter buttons shown as 3 above.  The measure list works with PP Utilities too, so you can format the DAX Expressions using that tool.

The tool currently finds references up to 7 levels deep.  If you need more than that you will need to modify the workbook logic yourself.

Video Demo

Here is a video of the workbook in action so you can see what it does in detail.

Bonus Fun

If you want some bonus fun, you can copy the first 2 columns from the Measure Dependencies table and paste it back into Power BI Desktop as a new table.

Then load the Force-Directed Graph custom visual and add the measure and referenced measure columns into the visual.

When I did this with my data, it gave me the following visual of the measure dependencies. It is a lot more fun when you have a live Power BI version as the graphic is interactive.

Download the Template

You can download the workbook using the form below.  Make sure you read the readme.txt for instructions on the easiest way to use it.

Relevant Articles

This is the second post in a series of articles.  You can read the first post in this series from the following link.

Part 1

Share?

Comments

  1. Hi Matt. Can’t wait to have a play with your revised file. But I’m not sure that the download option is working…I’m still waiting for the files to appear in my inbox some time after filling out the form

  2. It is technically possible to connect to SSAS Tabular, but I haven’t built this tool to do that. Feel free to reverse engineer what I have here so you can do it to SSAS Tabular.

  3. very cool! Is it possible to interact with a power pivot data model for the measure dependencies as well? I am now adding that chunk of SQL from your last post to extract the measures, but the dependencies function is very cool and would love to use!

    • It’s definitely possible, but it needs a different approach. There are various vba routines to extract and manipulate the tables, buttons to run the code, sheets with tables and Power Queries. These would need to be manually moved to your Power Pivot data model before it could be done the way I show here. I guess it could be made I to an addin (like Power Pivot Utilities). Maybe I will speak to Bertrand.

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x