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
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.
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.
Displays memory usage in your data model so you can see what tables and columns are chewing up the space in your workbook.
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.
Here is a video of the workbook in action so you can see what it does in detail.
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.
This is the second post in a series of articles. You can read the first post in this series from the following link.