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:

• Document Dependencies Between DAX Calculations by Chris Webb
• Measure Dependencies in Power BI by Matt Allington
• Visual Dependencies Between your DAX Measures by Imke Feldmann

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”

• Expand “Databases” to see the database name

 

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

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

• 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

• “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.

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.

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.You 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.To see what measures referenced calculated columns, just select “Calc_columns” from the “Referenced Object Type” slicer.

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