Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chandrarao
Frequent Visitor

Bi directional cross filtering/ambiguity

 I'm currently trying to create data model templates for our future users based on Salesforce data. When I'm trying to (re)create the data model however I'm running into the issue that I can't set relationships active due to the fact that it would create ambiguity.

 

Error:

You can't create a direct active relationship between Opportunity and Brand because that would introduce ambiguity between tables .... and ....

- The relationship you're creating lets you filter .... by ...., but Power BI Desktop allows only one filtering path between tables in a data model.

 

Looking for solution.

1 ACCEPTED SOLUTION

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/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

7 REPLIES 7
kevhav
Continued Contributor
Continued Contributor

I just got this same error for the first time ("Power BI Desktop allows only one filtering path between tables in a Data Model"); and at first, I was a bit surprised. I was not expecting it.

 

I had started a fact/dimension star schema, with just one fact table initially, and I found the bi-directional cross filtering to be quite useful. But then I tried adding a second fact table, and bang, this happened.

 

But when I thought about it, it makes sense. Say you had multiple filter paths from table DimA to table DimZ, like this…

DimA <> FactJ <> DimZ

DimA <> FactK <> DimZ

DimA <> FactL <> DimZ

Now, say you have a Power BI report in which you set a filter on DimA. So, how should DimZ be filtered? It's not clear.

 

But in this case, I kind of feel like it should "just work." I think I should be able to set up all of these bi-directonal cross-filters; and the three different paths—through FactJ, FactK and FactL—should act like an "OR" statement. That is, DimZ should be filtered to include all rows of DimZ that can be reached by at least one of the three filtering paths, after having filtered DimA.

 

I considered submitting this as an Idea, but I also kind of feel like it might be crazy. I'm not sure. Maybe it would cause issues in other models that are not star schemas? Maybe it would be too confusing? Maybe there is something I'm not considering.

 

But to me, it seems like it would be wonderful! What do you think, community? Would you find this useful? Or, am I missing something?

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. 

kevhav
Continued Contributor
Continued Contributor

Yes, 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.

Chandrarao
Frequent Visitor

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.

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/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks for your reply.

 

Can you explain in detail about step 4?

 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.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.