Simple Question

[RagnarokX66]

I think I'd get a lot of insight if someone answered me this-

I have a foreign table that contains a foreign key 'Account Number' and also the associated primary table with primary key 'Account Number' both in my power pivot data model where the foreign table is related to the primary table on the key.

How come when I use the Foreign Account Number field in Row Labels section of a pivot table and then add a field from the primary table also to the Row Labels section (say "Account Name") it results in something that looks like a cross join. I would expect a single Account Name to be pulled from the "Lookup" table for each Account Number. Instead, next to each Account Number every Account Name appears.

Thanks

 

[Matt Allington]

Very simply because power pivot automatic filter propagation ONLY flows from the one side of a relationship to the many side. From the dimension table to the fact table. From the lookup table to the data table. What ever language you want to use, it only automatically flows in 1 direction - by design.

So generally, use your lookup/dimension/one side of the relationship tabkes on rows, columns, filters, slicers and use the other table in values. If you need to have your data table filter the lookup table, you can read about that on my blog post for last week. Many to Many Relationships in DAX Explained - Excelerator BI

this is relatively advanced, but it has all the detail about the basics too.