BLOG

DAX Measure Dependencies in SSAS Tabular and Power BI

27-04-2019  0 Comment(s)

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”
  • 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

 

Comment Here

Comments

No Comments to Show

WE ALWAYS WORK WITH :