Pivot your table-relationships in Power BI and Power Pivot

While the relationships view of the datamodel provides a very good overview which tables are connected to each other, one cannot see at a glance on which field they are connected to each other.

This is where a pivot table-view of the field-connections can be really helpful:

Pivot-table-view:

Table-Fields-Connections in Pivot-View

 

Here you see the tables on the many-side in the rows and in the columns are the tables on the one-side (of course you can change that). Add some slicers if your model is very large.

This is the source-data in table-form which can sometimes also be helpful:

Table View:

Translated TMSCHEMA_RELATIONSHIPS

You fetch it via DMVs connected to your PBI-datamodel:

M-Code:

M-Code for Table-Field-Relationships

Code to download: MCodeDMV_TableRelationships.txt

Make sure you have only one PBI-workbook open, making sure that’s the one who this query will connect to.

Want to analyse your Power Pivot model in Excel instead?:

  • Use DAX-studio to read the model and export the 3 DMV’s to a txt which you then import or
  • Import your xlsx-file into Power BI so that its metadata can be fetched automatically.

Of course, this works for tabular-models in SSAS as well.

Subscribers can download the Excel file: RelationshipAnalysis2.xlsx

Enjoy & stay queryious 🙂

Comments (3) Write a comment

  1. This works very well, thanks for sharing! It’s nice to have a way to communicate to end users which tables have relationships in your model – especially useful for larger analysis services tabular models.

    Reply

    • Thx James, that’s what I hoped: I had developed it for me to quicker understand a new customer’s model and thought that it might helps others as well 🙂

      Reply

  2. Pingback: #Excel Super Links #59– shared by David Hager | Excel For You

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz