Impact assessment of DAX changes in 'golden dataset' measure

[Anonymous]

Is there a tool that can identify which reports use specific measures from given datasets? This would help us do targeted testing to assess whether DAX changes have caused visuals or filters to break.

For example, we have a 'golden dataset' for Sales and in Power BI Service I can see c.150 reports are connected to this dataset. If I change one measure in this dataset, only the reports that use the measure (in a visual or filter) would potentially be impacted, and those are the ones I'd want to test for unexpected results or broken visuals. Power BI Service currently has very limited information to use in impact assessment.

Given all the information regarding page and visual structure seems to be encoded in JSON (in the Layout file of the PBIX package), and all of that information is stored online (and potentially accessible via XMLA / API), in principle I assume that given a specific measure within a specific dataset, all accessible premium workspaces could be scanned to identify which reports use the measure (and on which page).

I don't think Azure Purview can do this. I think Power BI Sentinel (third party tool) can identify which columns are used somewhere but I don't think it goes to the above level of saying exactly where.

I'm sure it's not just us who have the problem that changes to DAX cause broken visuals, and there's no way to test for it - keen for solutions or ideas!

[Anonymous]

Hi @v-eqin-msft - thanks for responding

I'm afraid your suggestions don't work for this issue, and in fact I think there's no solution right now.

The issue is that I'd like to see which specific reports (multiple, separate files in different workspaces) use which specific measures in a single, central dataset. This is to check for broken visuals downstream of DAX development.

The solutions you suggested provide information on internal dependencies within one dataset. 

A requirement to solve the issue I've described is metadata access in reports, probably via the XMLA end-point. This isn't available yet and until MS changes that, I think there's no solution to automate this task.

Strange and disappointing, as error checking for broken reports seems to be a basic requirement of a reliable approach to continuous integration.

Here's an example to illustrate the functionality of a solution to this issue - for Looker, which (for now) has a more complete API for third party integration and automation: https://www.spectacles.dev/

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.

Hope to hear from you😀

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

You may try:

• Use DAX Studio

1. After opening the pbix file in Power BI Desktop, Go to File->New in DAX Studio.

2. A "Connect" dialog will appear. Select "PBI / SSDT Model" radio button and select the pbix file name from the drop down list. It will show all the pbix file that are currently open. 

3. Click "Connect" button.

4. Once you connect all the tables and measures in your data model from the pbix file will be listed on the left side.

5. Right-Click the table name that you want to check for dependencies and select the "Show Objects That Reference Table" option from the context menu that appear.

6. Step 5 will create a query and when you click run from the ribbon, it will list all the dependent tables, relationships, measures etc... that dependents on the selected table in "Query Result" tab.

• Use Tabular Editor

Install the free program Tabular Editor. Once installed, open your report and navigate to External Tools --> Tabular Editor. In Tabular Editor, navigate to a measure in the left pane, right-click and select "Show dependencies".

For more information, please refer to:

https://ssbi-blog.de/blog/technical-topics-english/how-to-identify-measures-not-used-in-your-pbix-fi...

https://www.sqlbi.com/articles/capturing-power-bi-queries-using-dax-studio/

https://www.thebiccountant.com/2020/01/01/tidy-up-power-bi-models-with-the-power-bi-cleaner-tool/

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.