Measure Dependencies in Power BI - Excelerator BI

Measure Dependencies in Power BI

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.

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

Power BI DAX Book

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.

Part 1

36 thoughts on “Measure Dependencies in Power BI”

  1. Hello, I’m not seeing a “template” or download option on the blog… Can you please send a direct link?

  2. Hi Matt I am interested in your file, but at this date May 2nd, 2023, it says that the file is not longer available

    1. Jason Cockington

      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.

  3. 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.

  4. 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.

    1. Matt Allington

      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.

      1. 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.

        1. 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.

  5. 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.

    1. 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.

  6. 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

  7. 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,

    1. 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.

  8. 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.

  9. virginia rodriguez

    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

  10. Johan van den Brink

    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. Johan van den Brink

      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.

  11. 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!

  12. 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!

    1. 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.

  13. 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.

  14. 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

Leave a Comment

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

Scroll to Top