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.

Leave a Reply

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

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