Forum Discussion
Excel Power Query/Data Model with Many to Many Relationship and Text Values in Pivot Table
Hi Excel Wizards!
I've trying to build a Pivot Table that utilises outputs from building assets and enables them to filter and search the data. I've managed to get a work around to avoid the many-to-many limitation in the Relationship functionality in the 'Data Model' but I am unable to achieve the final output that I'm after. I've created a mockup of what I'm trying to get to where 'blank' values for the count column are excluded and the description values only report the values relating to that 'Type' (I did attempt looking at merging tables in Power Query but wasn't sure if this was the right approach):
Current Pivot Table:
Desired Pivot Table:
If there are any excel wizards out there who would be able to help it'd be much appreciated!
Thanks in advance! Excel attached
dtbsmith
First, Excel data model doesn't support natively many-to-many or one-to-one relationships.Secondly, creating relationships first thing to do is to hide fields on many side from the client tool and don't use them in PivotTable (just best practice).
Third, when we go from one side to many and next from many to one that doesn't work, result as you have. I'd use virtual relationship here
DV := IF ( ISFILTERED ( Table3[Type] ) && [Count] <> BLANK (), CALCULATE ( CONCATENATEX ( Table3, Table3[Description] ), TREATAS ( VALUES ( Table3[Type] ), Table2[Type] ) ), BLANK () )PivotTable with this measure is
As a comment, I'd use more meaningful names for the tables rather than Table1, Table2, etc.
Please check in attached file.
dtbsmith
First, Excel data model doesn't support natively many-to-many or one-to-one relationships.Secondly, creating relationships first thing to do is to hide fields on many side from the client tool and don't use them in PivotTable (just best practice).
Third, when we go from one side to many and next from many to one that doesn't work, result as you have. I'd use virtual relationship here
DV := IF ( ISFILTERED ( Table3[Type] ) && [Count] <> BLANK (), CALCULATE ( CONCATENATEX ( Table3, Table3[Description] ), TREATAS ( VALUES ( Table3[Type] ), Table2[Type] ) ), BLANK () )PivotTable with this measure is
As a comment, I'd use more meaningful names for the tables rather than Table1, Table2, etc.
Please check in attached file.
- Thanks @Sergei, table names were just placeholder as I was trying to figure it out (end use will use formal titles). I've not delved much into Data Models (only stumbled across it when looking at Pivot Tables) but always good to know best practice. I was following this (https://youtu.be/4dGtJUc_jeU) video that explained how to achieve many to many relationships so was a bit of pot luck to get it working - if this isn't best practice do let me know.
In your mark up you show the relationship between Table 2 and 3 to be removed - is this because the formula that you drafted looks at table 3 rather than relies on a relationship between the two tables?Sorry for the confusing, we need that physical relationship for the [COUNT] measure, perhaps something else. I meant we don't need it for the description measure.
More about relationships
