DAX measures are the guts of each SSAS Tabular mannequin, Energy BI and Energy Pivot resolution. You write a number of DAX measures and also you doubtlessly reference a few of them in different measures. So the variety of DAX measures you write and reference them by way of different measures develop in a short time. Particularly in complicated options you will have lots of of DAX measures. Whereas your resolution works completely, to make a minor change or including a brand new measure to the answer or fixing an issue in your present measures could be such a ache within the neck. On this publish I’m going to take a step additional and present you a easy method to get the entire information mannequin dependencies then visualise the dependencies in Energy BI. You could find the obtain hyperlink on the finish of this publish.

A easy search in Google brings you a bunch of helpful articles speaking concerning the topic. A number of the bests, in my thoughts, are as under:

On this publish I exploit a DMV that offers us every thing we would like. ( Chris Webb already mentioned the DMV right here: Doc Dependencies Between DAX Calculations). Working the DMV we are able to see what measures are references by different measures, what columns are referenced within the calculated columns and rather more.

This can be a very helpful DMV that helps us getting a greater understanding of the mannequin we’re engaged on. We will additionally use this methodology for documentation.

This methodology is pretty easy, you simply have to run the next DMV on prime of your SSAS Tabular mannequin or your Energy BI Desktop file and Import the leads to Energy BI.

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

For Energy BI you’ll have to discover the native port quantity then you definately’re good to go. The one half that may not look very easy at first, could be discovering the database in Energy BI Desktop mannequin.

A simple approach, after you discover the native port variety of an opened Energy BI Desktop file, is to search out the database identify from SQL Server Administration Studio (SSMS) when connecting to the Energy BI Desktop mannequin:

• Open SSMS
• Choose “Evaluation Companies” as “Server Sort”
• Sort in “localhost:PORT_NUMBER” as “Server Identify” then click on “Join”

• Broaden “Databases” to see the database identify

• Open Energy BI Desktop
• Choose “SQL Server Evaluation Companies database” then click on “Join”
• Enter the “Server” and “Database”
• Ensure you choose the “Import” mode
• Broaden “MDX or DAX Question” and replica/paste the next DMV then click on OK

SELECT * FROM $System.DISCOVER_CALC_DEPENDENCY

• After importing information to Energy BI click on “Edit Queries” to open “Energy Question Editor”
• In Question Editor rename columns with extra consumer pleasant names

• I additionally do choose to capitalize every phrase within the “Object Sort” and “Referenced Object Sort” columns. You are able to do this by choosing each columns then proper click on and choose “Capitalize every Phrase” from “Remodel” sub-menu

• “Shut & Apply”
• The final step is to create a quite simple measure to depend the variety of dependencies within the present filter context

Object Depend = COUNTROWS('Mannequin Object Dependencies')

Now that we efficiently loaded information into Energy BI, it’s time to visualise the dependencies. As said earlier, you should use a community customized visible. I exploit “Pressured-Directed Graph”, however, you should use few different accessible customized visuals.

After importing the customized visible put it on the web page then put “Referenced Object” to “Supply”, “Object Identify” to “Goal” and “Object Depend” to “Weight”. Relying on how huge your mannequin is and the variety of mannequin dependencies you ought to see one thing just like the screenshot under.

As you see in the visible can look very messy and actually unreadable. The reason being that we’re displaying all mannequin dependencies. To make it extra readable and helpful we are able to put “Object Sort” and “Referenced Object Sort” columns on the web page as slicers. Now if you choose “Measure” in each slicers you’ll see measure dependencies.

It seems to be higher now, however, it’s nonetheless not good. Let’s format the visible to make it a bit higher.

• Click on the “Power-Directed Graph” visible
• Click on format tab from the “Visualization” pane
• Broaden “Hyperlinks” then flip the “Arrow” choice on. This can present the circulate of the dependencies
• Broaden “Nodes” and alter “Max identify size” to 50. The default is 10 which truncates the article names and makes them unreadable

You may add extra visuals to get extra insights out of your SSAS Tabular mannequin or Energy BI Desktop object dependencies.

Here’s a screenshot of the pattern I made for you.You may click on on a referenced object identify from the desk on the best hand facet to see all dependent measures within the Power-Directed Graph and in addition DAX expressions of the dependent measures within the different desk.To see what measures referenced calculated columns, simply choose “Calc_columns” from the “Referenced Object Sort” slicer.

You may obtain the Energy BI Template (PBIT) file from right here.