Basic PowerPivot Relationships

Question

I was having issues getting the results I was expecting using a large dataset with PowerPivot so I created two small tables to test.

Table1

   Store    Char1
   1        A
   2        B
   3        C
   4        D
   5        E

Table2

   Store    Char2
   1        One
   2        Two
   3        Three
   4        Four

I then created a relationship based on the columns "store" (this is just a model to illustrate the issue). When I make a Pivot table from Table1's Store and bring in Table2's Char2, I would expect one result for each number, basically

1 >> One, 2 >> Two, 3 >> Three, 4 >> Four.

What I'm getting is 1>> One Two Three Four, 2>> One Two Three Four, etc.

Having Char2 in Table1 would solve this issue, but again, I'm just trying to illustrate the issue because the solution down the road will not be that simple. I'd like to grasp what I've done wrong here first before moving forward.

I've researched the issue and have come across several issues like "How to handle one-to-many relationships in PowerPivot" and issues that are seemingly more complex. After failing with this simple model I'm wondering if perhaps I'm just approaching this entirely incorrectly and PowerPivot does not do what I thought it did.

Thanks!

Answer 1

Make sure you create the relationship with Table1 as the source table and Table2 as the related table:

When you add the Store and Char2 to the pivot table, it will show all possible combinations until you add some data from Table1 to the Values, then it will correctly show the results you want. If you get the relationship the wrong way around or try adding values from Table2, then you will see all the combinations again.