Forum Discussion

dtbsmith's avatar
dtbsmith
Copper Contributor
Jul 27, 2023
Solved

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-...
excel
Formulas and Functions
power bi
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 27, 2023

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 27, 2023

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's avatar
    dtbsmith
    Copper Contributor
    Jul 27, 2023
    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?

Resources