Update 21 Feb 2018: There are 2 versions of this localhost workbook. One works with the downloadable version of Power BI Desktop and the other works with the Microsoft App Store version. There were some technical issues in late 2017, but as at Feb 2018 everything seems to be working fine again.
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.
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.
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 here. 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.
Hi Matt, thanks so much, this is very helpful especially for big size models. Unfortunately the link you have on this blog doesn’t work for me and cannot download, maybe due to my laptop work firewalls/restrictions… Could you help with a new link somehow? Much appreciated.
Hello, I’m not seeing a “template” or download option on the blog… Can you please send a direct link?
Hi Alexa, this has been fixed now. You can download the template with the given link.
Hi Matt I am interested in your file, but at this date May 2nd, 2023, it says that the file is not longer available
Hi Denisse,
I just tested the download and it worked okay for me.
Please try again. If it fails to work a second time, I will send you a direct link.
You are a genius
Hey Matt,
just stumbled across your analysis workbook and it is exactly what I am searching for.
Unfortunately I cannot get it running. While the connection to power BI initially works well and I could also use the Pivot Table to analyse my measures, I struggle to get information on the Meausres and Measure Dependency Pages.
It compains that the query is pointing to another query and therefore cannot directly access the data source and asks me to recreate the Data combinations. Do you have an idea what might be the issue?
Thanks for your help.
Here is the complete error (umnfortunately in German):
Laufzeitfehler ‘1004’:
Abfrage ‘Measures’ (Schritt ‘AutoRemovedColumns 1’) verweist auf andere Abfragen oder Schritte und kann daher nicht direkt auf eine Datenquelle zugreifen. Erstellen Sie diese Datenkombination neu.
Hello Matt,
same issue i am getting you must have 1 instance of power BI. could you please help me to work on this please.
Steps I have done:-
1.I opened simple blank excel file
2.Opened template from my personnel downloaded from which you given,(I am not sure versions downloaded by giving details above.)
3.Power BI desktop file in my system only 1 opened.
4.Tried to refresh SSAS but showing error.
5.checked Open Folder it is showing only 1 path “C:\Users\****\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces”.
6.checked again refresh ssas but not succeeded .
could you please tell me in detailed.
In your step 5, try to delete the folder. What happens? If you can delete it, you are using the wrong template workbook – try the other one.
from where i can delete local system or in template . i am using Store Local Host Workbook 6.41 if this is not please can you share me the link to download.
Thank you so much.
There are 2 versions of the template file. You need to use the one that matches the version of Power BI Desktop you are using (the report server version is not supported). If you are using the STORE version, then use the STORE template, if you are using the manual download version, then use the “non store” version. In your step 5 above, you make the following statement
“5.checked Open Folder it is showing only 1 path “C:\Users\****\Microsoft\Power BI Desktop Store”
When you open the folder by clicking the button, try to delete the folder. What happens? If you can delete it, you are using the wrong template workbook – try the other one.
Is this still functional? I tried to use this today but it failed saying I should only have one instance open. I only have one open, and when I follow the steps on the connection tab I found there are no folders in the referenced location.
Yes it is. Did you read the read the readme file? The only version that is not supported is Report Server. There are 2 versions of the file for each PBID version that work, one for the store version and one for the msi install version. Try using the other one.
Does Power BI has dependency funcationality?
I m a MS Project user and know it very well, without ‘dependency’ function , project planning doesn’t have that much meaning
Hi Matt, I’ve downloaded the template and received the following error message, I know this error message is covered in the read me file, though the 1-6 steps provided do not change the outcome. I still mange to receive the same error message after completing the 6 steps. Can you please advise on an alternative potential fix?
“Run-time error ‘1004’:
[DataFormat.Error] The file name ‘C:\Users\username\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspaces4984
is longer than the system-defined maximum length.”
Thanks,
mmm, I’m not sure about that error, sorry. If you contact me via the website, maybe we can make a time next week for me to take a look – i’m interested to see it.
Hi Matt,
I have the same exact problem as above.
Did you find anything on this?
Thanks a lot!
No, I never got to investigate. I could find 5-10 mins to have a look with your if you like. Please contact me at http://xbi.com.au/contact
Hi I seem to have an error that keeps coming up within power query that makes it all fall down unfortunately 🙁
Formula.Firewall: Query ‘Measure_Dependencies’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
This is covered in the readme file
Hello,
I have a problem for import an excel archive. When a try to import power bi tell me this error:
“Detalles: “Excel Workbook: El proveedor ‘Microsoft.ACE.OLEDB.12.0’ no está registrado en el equipo local. Es posible que sea necesario disponer de la versión de 64 bits del proveedor OLEDB del motor de base de datos de Access 2010 Access Database Engine para leer “Copia de 201908_DATOS_COMPLETOS_RESUMEN_EJECUTIVO_ANEXO (002)PRUEBA ANA.xls”. Para descargar el software cliente, visite el sitio siguiente: https://go.microsoft.com/fwlink/?LinkID=285987.”
Could you what about this problem? Thanks
what do you mean an “excel archive”?. This may help you https://exceleratorbi.com.au/importing-xlsb-into-power-bi/ You may need to install the data connectors listed in the error. you can read about that here https://exceleratorbi.com.au/64-bit-power-bi-desktop-32-bit-office-can-do/
Hi Matt,
It says “Exactly one Power BI instance has to be open” but I do have only one (I do have a normal SSAS Tabular instance though).
Johan
1. Go to the Connection Tab in the Excel Workbook CONNECTION tab??
2. Click the button “Open Folder”
3. If there is more than 1 folder visible, delete the one(s) not associated with the current open Power BI Desktop file using the creation date as the reference.
I think this tool is great, though I am on the latest version of Excel so the Measure Dependencies doesn’t work as expected. Do you expect to release a new version of this? Many thanks!
Are you able to provide more information other than “doesn’t work as expected”?
Wonderful tool. Thanks a lot.
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.
Thanks Matt!
and I just downloaded PP utilities, wonderful little tools there too. Thanks!
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.
can it also connect to SSAS Tabular model?
Hi Matt,
there seems to be limitations with the exposure of calculation dependencies (see here for example: https://blog.gbrueckl.at/2016/04/visualizing-ssas-calculation-dependencies-using-powerbi/)
But you can still evaluate them using good old M-code like here: http://www.thebiccountant.com/2015/12/18/visualize-dependencies-between-your-dax-measures/
Awesome discovery! Thanks, Matt!
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
Jeff, the file should download immediately to your downloads folder
The inclusion of the force directed graph is very clever.