Power Pivot appears to ignore relationships

[csabz09]

Hi All!

I am trying to use a filter table to simply filter two other tables in Power Pivot, however, it looks like the relationship is not respected as soon as I add 'col1' from my first table and 'col2' from my second table.

I have a 'filter table' that I am trying to use to filter two other tables with it.

My filter table looks like this:

The first table it filters looks like this:

And the second table it filters looks like this:

I have set up the relationships as per this screenshot:

My desired output is this:

However, when I try to construct such a pivot table, it looks returns all col1-col2 combinations as if the filter did not even exist:

Here is the link to the file if anyone wants to take a look: weird.xlsx

Any tips on how to make this work? When I try to do the same in Power BI, it works well so I am extremely confused.

Thanks a lot!

 

[Alex Blakenburg]

The relationship you have does not appear to make sense and Power Pivot can't work it out.

When you assign a 1 to 2-ac, is it supposed to go against 2-xy or 2-xz or both ?
That type of relationship won't work in the Data Model.

 

[csabz09]

Ah I see, thank you.

 

[csabz09]

Either way I managed to make it work as per my requirements using a DAX formula, which does a row count for both tables that should be subject to the filter, and only returns values when the row count in both tables is larger than 0, and in any other case it returns BLANK. This way I only get category values that indeed belong to the keys coming from the filter table.

sum_val =IF(COUNTROWS(table1) > 0 && COUNTROWS(table2) > 0, SUM(table2[value]), BLANK())

Output:

 

[csabz09]

(And sorry for the wrong terminology, I should have called the join keys of my tables to be filtered 'foreign keys')

 

[Alex Blakenburg]

Thanks for letting me know. Glad you worked out a way to come up with the result you were after.