Excel 2013 Power Pivot: Pivot Table not showing related fields

[cnegroni]

Hi All:

In Excel 2013 I have 2 tables and I built a model with those 2 tables (I used the function 'Related' in one of them and it worked so the model it is Ok). However, when I built a Pivot Table for this model the relationship didn't work:

- If I add a field from the first table to the Pivot Table and then I add a field from the second table, then for each value in the first table all the values in the second table are listed, so why I don't see just the related field?. Do I need to do something else?. By the way, this is my first use of Power Pivot.

The file it is located in: http://wikisend.com/download/409548/PowerPivot_Test.xlsx

Thank you very much
Regards​

Carlos Negroni

 

[scottsen]

I can't figure out how to download the file (it tries to make me download p.txt, which is a list of email addresses )

 

[cnegroni]

Hi Scottsen:

You are right, maybe the "Time to live" of the file expired. I zipped the file and the new link is: Wikisend: free file sharing service

Thank you very much

 

[scottsen]

I usually find this a little weird too. But... the "right thing" doesn't happen until you drop something in the values portion of the pivot table. Then you will get what you want.

 

[cnegroni]

Hi scottsen:


Thank you very much, you are right, if I add RoleID to the Values section in the PT then it works. I really appreciate your help.


Regards


Carlos Negroni

 

[Tianbas]

I had the same issue some time ago. Normally we always have some measures in the value field of pivot table but this time a colleague wanted to use PowerPivot to join two tables with different attributes for the same unique key field. When we put this in a pivot all data have been shown as if there was no relation. It took me a while to find out that I need to put someting in the values field to make it work

My key learning from that: Always put something in values first, even if its something stupid like the count of a key column

 

[cnegroni]

Hi Tianbas:

Thanks for your suggestion, although PowerPivot shouldn't work that way. Anyway, I'll follow Scottsen advice and yours of course. Thank you very much. I got an answer to my question.

Regards

Carlos Negroni