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/
Community ChampionBi 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/
Thanks for your reply.
Can you explain in detail about step 4?
- MattAllington
To clarify what I mean in point 4, if you have 2 data tables that connect to the 2 lookup table AND you need bi directional relationships for all of them, then you need to move to a different design where you leverage the many to many DAX pattern. The solution to this problem is explained in detail in the links I provided.