Issues with Pivot table: Data Model is not applying relationship

[Royden Carneiro]

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 table. I'm not sure what I'm doing wrong. The table structure is very simple. 

My Tables:

 Data Relationship

 Pivot Table

My Expected Results are: 

 James Corbin: BA

Jim Jam: PM

Lara Croft: Java, BA, PM

Row Low: Java, .NET

What am I doing wrong? Thanks in advance.

[#2]

Hi Royden,

Right tool will be Power Query, please see attached

[#3]

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.

[#4]

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

[#5]

Hi Sergi,

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

[#6]

Hi @Royden Carneiro ,

I am having a similar issue. I have already created all auxiliary tables with unique entities list, to act as a link between tables. But it does not work still...

I have posted more details of the specific case in this post:

https://techcommunity.microsoft.com/t5/excel/pivottable-error-with-relationships-using-data-model/m-...

Any help is appreciated. Thanks!

[#7]

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.

[#8]

@Karen1270 

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

[#9]

@Sergei Baklan 

Your sample file was exactly what I needed. Problem solved. :)

Many thanks!

[#10]

@Karen1270 , you are welcome