Forum Discussion
Bi directional cross filtering/ambiguity
Bi directional relationships are one way of solving a specific problem where you need to filter a lookup table from the contents in a data table. Not every lookup table will need this and there are other ways to solve the problem. I suggest you
1. turn off bidirectional cross filtering on all your tables.
2. Hide the foreign key in every data table. This way users won't accidentally use is instead of the primary key in the lookup table.
3. When (and only when) you need a bi directional relationship for what you are doing, turn it on for that single table.
4. If you ever need 2 that conflict, then use the many to many pattern. I explain it here https://www.powerpivotpro.com/2014/08/filters-can-flow-up-hill-via-formulas-that-is/ and also here http://exceleratorbi.com.au/many-many-relationships-dax-explained/
Your OR Logic can be even AND. That makes more sense. so Dim A in Dim Z which passes all three facts just like filters.
Continued ContributorYes, that could be another way! But, when would this come into play? When you have a single report page, or dashboard, showing...
- Dimension data from DimA
- Dimension data from DimZ
- Fact data from multiple related fact tables, like FactJ and FactK
...Now, say you have a single report page or dashboard like that, and you apply some filter/slicer to DimA.
And, say that applying this filter to DimA subsequently filters FactJ, which subsequently filters DimZ to exclude rows Z1, Z2, Z3 and Z4.
And, say that applying this filter to DimA subsequently filters FactK, which subsequently filters DimZ to exclude rows Z3, Z4, Z5 and Z6.
DimZ rows Z5 and Z6 are related to, and "describing," the measures from FactJ; and DimZ rows Z1 and Z2 are still related to, and "describing," the measures from FactK. That's why I think it should be "OR" logic: we still need DimZ rows Z1, Z2, Z5 and Z6 in the report/dashboard, even though these rows aren't included by all of the filter paths.