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/
Continued ContributorI 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
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.