CODEWITHSUNDEEP
excelpivot-tablepowerpivotdatamodel
OldKingCole
OldKingCole

Reputation: 125

Excel Data Model Relationships Pulling Entire Tables

Going a bit crazy over this issue. I have a Data Model with several tables, each of which has a key field designed to connect it to the others. For example, "age_product" concats an age field and a string field for a product name. In my primary table, these will appear multiple times because the same product/age will appear in multiple states, etc. but in another "product" table, these are unique. Only one entry for each product/age combination, and a price that I need to pull.

I connect these two tables by the age_product key. Then I create a pivot table and add age and product as rows. Good so far. I add fields from the primary table. Good so far. I add the average price from the product table, and its identical in every age.

Out of curiosity, I filter to just one product, one age, then double click the pivot table to get the values that are getting pulled into this calculation. It shows that for Age 0 and Product X, every single entry from the product table has been pulled in. Its very clearly showing my age_product key and showing every age, every product, connecting them all to Age 0 Product X despite the key field not matching for those.

This happens identically for every age/product combo. Thank you so much for any insight you can provide, I don't know what to do. There's a unique age/product key but the Data Model is taking Age 0/Product X and matching it to every single combination despite mismatched keys.

Upvotes: 1

Views: 673

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9052

Bi-directional relationships are not possible in Power Pivot. As such, filtering via relationships only ever propagates from the one to the many side, not vice versa. Hence, in your case fields from the product table are capable of filtering the primary table, though fields from the primary table are not capable of filtering the product table.

One solution would be to create single-column 'bridge' tables for each of the fields from the primary table which you wish to use in your report, and create relationships between these bridge tables and your two current tables. Since the fields from these bridge tables could not by definition be on the many side of their relationship with the other two tables, they would be capable of filtering said tables.

Upvotes: 2

Related Questions