A Present for You: compare relationships in Power BI models
I’ve had to compare relationships in big models a lot, so I made a Power BI template to make it easy for me. Now, I share this tool with you.
This template file will get the relationships from 1 or two files. Then, it compares the relationships from the first file to the second file. Since a relationship could have either table as a first or a second table, I index the table and remove duplicates after comparing.
power-bi-metadata/Compare Relationships.pbit at master · fpk3/power-bi-metadata (github.com)
Tool to get/compare relationships from an open Power BI Desktop file using DAX Studio.
(1) Open a report in Power BI Desktop.
(2) Open DAX Studio and connect to the open report.
PARAMETERS:
(a) localhost is displayed in the bottom frame of DAX Studio. Type the numbers following localhost.
OR get XMLA connection beginning with powerbi:// in Premium tab of dataset settings
(b) catalog_name is a text value, which can be found by running this query in DAX Studio and using the result: SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS
(c) for second model repeat the steps. If no second model, then leave both fields blank.
• DAX Studio is available at https://daxstudio.org/
NOTE: Approve Native Queries when prompted.
IMPORTANT: Use Microsoft authentication for XMLA endpoint, and use Window authentication for localhost. If you use Microsoft authentication for XMLA, it will fail, and then you’ll have to figure out how to clear the credentials and start again.
Can’t load two PBIXs at the same time? Export .csv one at a time and then use this Excel file:
power-bi-metadata/Relationships Compare.xlsx at master · fpk3/power-bi-metadata (github.com)
Ways to use:
Open up two versions of a pbix file, get localhost and catalogname from DAX Studio and then open the template to compare two files.
Compare two files in premium workspaces using connection info in dataset settings.
Compare an open .pbix file with a published file in a premium workspace.
For big models, open one version at a time and export the Relationships.csv.
Then input the file name and location in Relationships Compare.xlsx to compare.