Forum Discussion

Royden Carneiro's avatar
Royden Carneiro
Copper Contributor
Dec 07, 2018

Issues with Pivot table: Data Model is not applying relationship

Hi there,   What I'm trying to do is build a pivot table that utilizes a data model. Unfortunately, for some reason, the relationships described in the data model are not being applied to the pivot...
BI & Data Analytics
Excel Desktop
Royden Carneiro's avatar
Royden Carneiro
Copper Contributor
Dec 07, 2018

Thank you Sergi, I took a look at your solution. Does that mean Pivot tables don't work with the data models. I was trying to stay away from a marged table as I have a large data set and would like to leverage data relationships and pivot tables.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 07, 2018

    Royden, Pivot Table works fine with data model, that's the main method to show data model results.

     

    In your case in one-to-many relationships build pivoting  from many to one. To your middle table in Power View add name and role from the related table

    and pivot it

    Please see attached

    • Karen1270's avatar
      Karen1270
      Copper Contributor
      Feb 16, 2023
      Thank you!
      I was running into same exact problem - was able to figure out path forward by studying the PowerQuery steps in your sample file.
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Feb 17, 2023

        Karen1270 

        Thank you. Didn't catch, did you solve your problem or still have a question?

    • Royden Carneiro's avatar
      Royden Carneiro
      Copper Contributor
      Dec 12, 2018

      Hi Sergi,

       

      Thank you for this. You've given me an idea on how to accomplish this.

Resources