Forum Discussion
Advocate IISingle crossfiltering ignored for columns?
Super UserWhat the above explanation from Alberto says is that, even in the First Visual, the filtering you describe happened through the expanded Store table because it contained District[District] in it.
Yes that is what I was saying in my previous response. So I think we in agreement here. That visual 1 works as expected because District is in the expanded Store table. So let's focus now on the behaviour of Visual 2
How that works is through expanded tables. Please see below from Alberto's article.
The filter applied on Product[Product] follows the relationship between Product and Sales, thus filtering the Sales table too. A better description of that same filter propagation uses the concept of expanded tables. When you filter Product[Product], all the tables that contain that column – either native or related – are filtered.
Thus, Sales is filtered by Product[Product] because the expanded version of Sales contains Product[Product].
Each expanded table contains both native and related columns. Native columns are the ones originally present in the table. Related columns are all the columns of related tables, added to the original table through table expansion.
Yes, I did the technical review on Alberto and Marco's first book on the tabular engine. I know the concepts of extended tables and filter propogation well. When I read the above I see that it describes the behaviour that we are seeing in Visual 2.
If you translate the statements above to your schema - Store is filtered by District because District is in the extended table for Store.
But District is NOT filtered by Store as Store is not in the extended table for District.
So when you do DISTINCTCOUNT( District[District] ) , because you are explicitly referencing the District table, then engine looks in the extended tables for District to see if it can propogate any of the current filters, but it cannot because District does not have any extended tables in your schema (ie. there are no tables on "one" side of a "one to many" relationship)
Advocate IIYes, I did the technical review on Alberto and Marco's first book on the tabular engine. I know the concepts of extended tables and filter propogation well.
So then, we are in agreement that District[District] is also a column of expanded Store table.
Now let's try to guess what Power BI rendering engine thinks.
First Visual:
Power BI engine thinks: "This visual needs Store[Chain] and District[District]. I could have chosen expanded District table but it does not have Store[Chain], so I decide not to chose it. So I decide to choose expanded Store table because it contains both columns."
Second Visual:
Power BI engine thinks: "This visual also needs the same two columns as above. I can choose expanded Store table as before because it contains both columns, but I will NOT chose it this time! Instead, I will chose expanded District table and then say; I can't get to Store[Chain] from this!!"
It is this contradicting "thinking" behaviour that needs a consistent explanation.
Input columns for both visuals are the same, so it is logical to assume that the both visuals should chose the same expanded table.
We are not being consistent if we say that for the First Visual, the rendering engine chooses an expanded table that has both the required columns but for the Second Visual, the rendering engine doesn't do that. That is the problem I have with d_gosbell answer.
- d_gosbell
So then, we are in agreement that District[District] is also a column of expanded Store table.Yes, it was never in contention that Store can expand to District
Now let's try to guess what Power BI rendering engine thinks.We don't really need to guess, Alberto's article has a pretty good explanation.
I could have chosen expanded District table
This is not correct. In your schema District cannot be expanded at all. It does not have any relationships pointing in the right direction to allow it to expand.
Second Visual:
Power BI engine thinks: "This visual also needs the same two columns as above. I can choose expanded Store table as before because it contains both columns,
No, it does not think this at all. The second visual has one column Store[Chain] and one measure. Measures are calculated in a filter context. So as it calculates each row of the table it picks up the current value of Store[Chain] and puts that in the filter context. It then looks at the fact that it's doing a distinct count over District[District] it looks at district and realizes that it cannot expand District to Store as the relationship is pointing the wrong way so it cannot apply the current value of Store[Chain] to the District table, so the District table is effectively unfiltered at this point so the DistinctCount that is returned is for the entire table.
This behaviour is consistent and explainable although I would agree that sometimes it can seem inconsistent until you dig into it.
I hope this makes sense, but if my attempts at explaining this are still not making sense then I'm sorry and I might bow out and let someone else see if they can attempt to explain these concepts in a different way.