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/
I know what is causing the issue that I get the ambiguity errors and that by putting the relationship between tables on single direction that it solves the problem with the errors I’m getting. That is not the issue however and I need bi directional filtering for my reports.
The issue is on the architectural level.
When using multiple tables that have looped relationships between them (inherent to the Salesforce data model), that’s when I’m getting the ambiguity issues. My goal is to recreate (parts) of the Salesforce Data model as accurate as possible in Power BI so our business users can use them for their reports because they have no data modeling experience.
So what I need is someone with expertise on Using the Power BI Salesforce Connector in Power BI.
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.