DAX Measure Dependencies in SSAS Tabular and Power BI

DAX measures are the heart of every SSAS Tabular model, Power BI and Power Pivot solution. You write lots of DAX measures and you potentially reference some of them in other measures. So the number of DAX measures you write and reference them via other measures grow very quickly. Especially in complex solutions you may have hundreds of DAX measures. While your solution works perfectly, to make a minor change or adding a new measure to the solution or fixing a problem in your existing measures can be such a pain in the neck. In this post I’m going to take a step further and show you a simple way to get the whole data model dependencies then visualise the dependencies in Power BI. You can find the download link at the end of this post.

A simple search in Google brings you a bunch of useful articles talking about the subject. Some of the bests, in my mind, are as below:

In this post I use a DMV that gives us everything we want. ( Chris Webb already discussed the DMV here: Document Dependencies Between DAX Calculations). Running the DMV we can see what measures are references by other measures, what columns are referenced in the calculated columns and much more.

This is a very useful DMV that helps us getting a better understanding of the model we’re working on. We can also use this method for documentation.

How It Works

This method is fairly simple, you just need to run the following DMV on top of your SSAS Tabular model or your Power BI Desktop file and Import the results in Power BI.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

For Power BI you’ll need to find the local port number then you’re good to go. The only part that might not look very straightforward at first, would be finding the database in Power BI Desktop model.

An easy way, after you find the local port number of an opened Power BI Desktop file, is to find the database name from SQL Server Management Studio (SSMS) when connecting to the Power BI Desktop model:

  • Open SSMS
  • Select “Analysis Services” as “Server Type”
  • Type in “localhost:PORT_NUMBER” as “Server Name” then click “Connect”

Connect to Power BI Desktop Model from SSMS

  • Expand “Databases” to see the database name

Browse Power BI Desktop Model in SSMS

 

Getting Tabular Model Object Dependencies Data from SSAS Tabular

  • Open Power BI Desktop
  • Select “SQL Server Analysis Services database” then click “Connect”
  • Enter the “Server” and “Database”
  • Make sure you select the “Import” mode
  • Expand “MDX or DAX Query” and copy/paste the following DMV then click OK
SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

Import SSAS Tabular Metadata to Power BI

  • After importing data to Power BI click “Edit Queries” to open “Power Query Editor”
  • In Query Editor rename columns with more user friendly names

SSAS Tabular Model Object Dependency with DMV in Power BI

  • I also do prefer to capitalize each word in the “Object Type” and “Referenced Object Type” columns. You can do this by selecting both columns then right click and select “Capitalize each Word” from “Transform” sub-menu

Capitalize Each Word in Power BI

  • “Close & Apply”
  • The last step is to create a very simple measure to count the number of dependencies in the current filter context
Object Count = COUNTROWS('Model Object Dependencies')

Visualising Tabular Object Dependencies in Power BI

Now that we successfully loaded data into Power BI, it is time to visualise the dependencies. As stated earlier, you can use a network custom visual. I use “Forced-Directed Graph”, but, you can use few other available custom visuals.

After importing the custom visual put it on the page then put “Referenced Object” to “Source”, “Object Name” to “Target” and “Object Count” to “Weight”. Depending on how big your model is and the number of model dependencies you should see something like the screenshot below.

Using Force-Directed Graph Custom Visual in Power BI

As you see in the visual can look very messy and literally unreadable. The reason is that we’re showing all model dependencies. To make it more readable and useful we can put “Object Type” and “Referenced Object Type” columns on the page as slicers. Now if you select “Measure” in both slicers you’ll see measure dependencies.

Using Force-Directed Graph Custom Visual in Power BI

It looks better now, but, it is still not good. Let’s format the visual to make it a bit better.

  • Click the “Force-Directed Graph” visual
  • Click format tab from the “Visualization” pane
  • Expand “Links” then turn the “Arrow” option on. This will show the flow of the dependencies
  • Expand “Nodes” and change “Max name length” to 50. The default is 10 which truncates the object names and makes them unreadable

You can add more visuals to get more insights from your SSAS Tabular model or Power BI Desktop object dependencies.

Here is a screenshot of the sample I made for you.DAX Measure Dependencies in SSAS Tabular and Power BIYou can click on a referenced object name from the table on the right hand side to see all dependent measures in the Force-Directed Graph and also DAX expressions of the dependent measures in the other table.DAX Measure Dependencies in SSAS Tabular and Power BITo see what measures referenced calculated columns, just select “Calc_columns” from the “Referenced Object Type” slicer.DAX Calculated ColumnDependencies in SSAS Tabular and Power BI

You can download the Power BI Template (PBIT) file from here.

12 thoughts on “DAX Measure Dependencies in SSAS Tabular and Power BI

  1. Hi there, thank you so much for your post. I followed the instructions, but still not be able to get the calc dependency. When I run the query SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY I got this result: Details: “AnalysisServices: Nó de dependência sem suporte descoberto.” [in English something like *Dependency node without support discovered.*
    How can I find out why this error occurred?
    Thanks in advance!

        1. Thanks Nathany for sharing the link to JP’s problem in GitHub.
          Look, if you’re using DISCOVER_CALC_DEPENDENCY DMV to document your model/report or for auditing purposes then I have a good news for you.
          You can use my tool, Power BI Documenter.
          Then check the “Dependencies” tab that you can see a graphical representation of your DAX object dependencies or download the dependencies as CSV.
          Find out more about Power BI Documenter here: https://www.datavizioner.com.
          To learn how to use Power BI Documenter, check this out: https://www.datavizioner.com/blog/how-to-use-power-bi-documenter.
          I tested the file JP uploaded to GitHub in Power BI Documenter and it worked like a charm.
          But if you’d like me to investigate more, DISCOVER_CALC_DEPENDENCY, then please upload your file to GitHub and share the link here and I’ll have a look as soon as I can.
          By the way, if none of the above is an ideal choice for you, I have another post about SSAS/Power BI Documentation in Power BI without DMVs.
          Cheers.

          1. Hi Kseniia,

            Welcome to BIInsight.com.
            Apparently the issue with the DISCOVER_CALC_DEPENDENCY is raised a couple of months ago. Microsoft is already aware of that but there is currently no specific time suggested for a fix.
            What is your use case?
            If you’re using it for documentation purposes then you can use my tool, Power BI Documenter which gives you all dependencies, even in RLS level. Here is the direct link to the app: https://app.datavizioner.com.
            To learn more check this out: https://datavizioner.com.
            Hopefully that helps.
            Cheers

          2. Hi I’ve managed to work around the issue by refactoring all my m expressions to make sure they don’t reference to any other queries. That has fixed it. I don’t think the DMV is handing the M-expression and/or partition dependencies properly.

          3. Hi Brian,

            Thanks for sharing your experience.
            I suppose we use DVMs to get metadata from our model, so changing transformation steps to be able to get DMV results doesn’t sound right.
            I think the issue started happening after the new enhances metadata rolled out but I am yet to test/confirm it.
            Cheers

    1. Hi Nathany it can happen if you’ve got a broken measure, calculated column or calculated table so that’s the first thing to check. But I have run into this with no broken calculation which I believe is a bug that Darren God (DAX Studio,) has reported to Microsoft. I’ve tracked it down to the DMV being unable to process Power Query queries that refer to other queries ( example a query that merges in another query). I’ve been able to temporarily get around the problem by making all my Power Query m expressions be entirely self contained (e.g bring the entire definition of the other query you want to merge into the query you are using it from)

      1. Hi Brian, thank you very much for the workround. Coincidentally (I’m shoked! rsrs) I’ve just applied one M step at a time to see when the DISCOVER_CALC_DEPENDENCY brokes and I got the same as you: I got the error in a merge step. Your reply found me just in time to conclude what is happening. Thank you so much, I will share this bug with my team!

        1. No problem. I’ve raised a support ticket with Microsoft, otherwise they don’t tend to prioritise things unless they see multiple customers being impacted. It’s a horrible work around to have to unpinned my data load architecture but really need to understand the Dax dependencies more at the moment ?

    2. Apologies just read the threads below! (darn mobile view) Anyway, hopefully you can use the work around if you are stuck.

Any thoughts? Share it with us here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.