Allow multiple filtering paths between tables?

[kevhav]

I wonder what the Community thinks about this...

I recently got this same error for the first time: "Power BI Desktop allows only one filtering path between tables in a Data Model."

At first, I was a bit surprised. I was not expecting it. I had started building 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 "discovered" this error.

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

DimA  <>  Fact1  <>  DimZ

DimA  <>  Fact22  <>  DimZ

DimA  <>  Fact333  <>  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 feel like it should "just work." I think I should be able to set up all of these bi-directonal cross-filters; and the the three different paths—through tables Fact1, Fact22 and Fact333—should act like an "OR" statement. That is, upon applying some filter to the DimA table, the DimZ table should be filtered to include the rows of DimZ that can be reached by at least one of the three filtering paths.

For my purposes, I think this would be great! And I have considered submitting this as an Idea for voting. But, I also have this feeling that it might be crazy. Like, maybe I haven't realized what all the consequences would be.

What do you think, Community? Would you find this useful? Or, if it would do more harm than good...why? (Maybe it would cause issues in other models that are not star schemas? Maybe it would be too confusing, for end users, and/or for Power BI developers?)

[Anonymous]

I would find this more confusing than useful. As a report developer, I set my model to behave in a specific way. If Power BI starts making it "just work" I have no guarantees that my model will behave as expected - or that it would reproduce the same results every time.

Generally with star schemas, whether in Power BI or in an Analysis Services model, Facts do not filter dimensions - the filter is one way from Dim > Fact

[kevhav]

Yeah, I get that.

But also, the one-way filtering on relationships is the default, and I wouldn't propose changing that.

And, if you were able to set up multiple, bi-direcitonal filter paths in the way I am thinking...then the results would still be predictable and repeatable. (For example, upon applying some filter to the DimA table, the DimZ table would be filtered to include the rows of DimZ that can be reached by at least one of the filtering paths.)

[v-qiuyu-msft · Accepted solution]

Hi @kevhav,

In my opinion, it's not useful. As DimA and DimZ with loops, cross filtering can create an ambiguous set of relationships. If we enable cross filter in this kind of pattern, data couldn't be filtered correctly. For more explaination, see: Adjusting cross filter direction for a complex set of tables of relationships.

Best Regards,
Qiuyun Yu