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?

      • Karen1270's avatar
        Karen1270
        Copper Contributor
        Feb 17, 2023

        SergeiBaklan 

        Your sample file was exactly what I needed. Problem solved. 🙂

        Many thanks!

  • 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