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.
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!
Hi Nathany,
Welcome to BIInsight.com.
Are you using SSAS? if yes, then what version?
If you are using Power BI then you shouldn’t have any problems at all.
Cheers.
I’m using Power BI and Dax Studio, this guy got the same error (Unsupported dependency node discovered), but it seems that no one could figure it out https://github.com/DaxStudio/DaxStudio/issues/236
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.
Thank you for sharing this. I have the same problem. Can I ask you if you solved this problem?
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
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.
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
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)
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!
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 ?
Apologies just read the threads below! (darn mobile view) Anyway, hopefully you can use the work around if you are stuck.
Hi there,
Thanks for publishing such an informative post. I have been trying to connect to one of our report’s model via this method but I see that there are no databases listed.
Is this because the report is using Live Connect to an Azure Analysis Services model? I have tried using tools like DAX Studio and Tabular Editor and none of them are able to connect to a report that uses LiveConnect to AAS. Any thoughts?
Regards,
Pushkaraj
Hi Pushkaraj,
When you connect live to an instance of SSAS (either tabular or multidimensional) or AAS, the data model sits in the AS side not in Power BI. Therefore you cannot see the Tabular model object dependencies.
So to get the dependencies of your AS model, just connect to your AS instance directly from the Power BI template (PBIT) file provided in this blogpost.
It should work perfectly fine.
Please let me know if you could make it or not.
Cheers
Hi Soheil,
Many thanks for your reply. I see that your template file is really useful for finding measure dependencies in the model.
However, I am trying to find dependencies of the measures defined within the report. As you rightly mentioned, when the report is using Live Connect, the data model seems to be residing in the AS server.
Because of this, I am unable to find dependencies of the measures defined within a Power BI report, Do you think there is any way to achieve this?
Thanks,
Pushkaraj
Have you tried my tool, the Power BI Documenter?
It is more than a documentation tool; it is a tool to analyse your Power BI reports and data model.
Power BI Documenter is capable of detecting Report Level measures which is exactly you are talking about. Give it a go, we have a free version, you might find it helpful. You can learn more about the Documenter here: http://www.datavizioner.com