Forum Discussion
Advocate IISingle crossfiltering ignored for columns?
Super User
I would like an explanation preferrably from someone who understands this. For example, do "expanded tables" have anything to do with this? Is the expanded version of Store table used in the first situation? If so, why doesn't DISTINCTCOUNT() not apply on the expanded version of Store?
Yes, expanded tables do come into play here. But the set of expanded tables is not static, it depends on the context of your calculation and which columns you are calculating over.
Tables only expand into other tables that are "pointing" at them. So if you do any calculations over columns in the Store table it will consider columns in the District table because the arrow in the relationship from the District table points at Store.
But if you do calculations over District it will not include any of the columns from the Store table in it's expanded tables as the relationship is pointing in the other direction (the arrow is pointing away from District). So doing DistinctCount( District[District] ) will not "group by" any columns in Store as the Store table is not part of the expanded tables for District so all the engine can do is to repeat the unfiltered total for each row.
Advocate IISo if you do any calculations over columns in the Store table it will consider columns in the District table because the arrow in the relationship from the District table points at Store.
But if you do calculations over District it will not include any of the columns from the Store table in it's expanded tables as the relationship is pointing in the other direction (the arrow is pointing away from District). So doing DistinctCount( District[District] ) will not "group by" any columns in Store as the Store table is not part of the expanded tables for District so all the engine can do is to repeat the unfiltered total for each row.
Column District[District] appears in two tables; (1) District table, and (2) expanded Store table.
So, it is incorrect to assume that DISTINCTCOUNT( District[District]) will operate on District table; it has the option of operating on either of the above tables. Which table is chosen and why, appears to be a Power-BI's internal implementation decision which needs an explanation. In my opinion, a consistent answer should be able to address the two table examples ("First visualisation" and "Second visualisation") I have given in the first first post, and consistently explain why the first visualisation used the expanded Store table but the second visualisation did not even though it could have.
- d_gosbell
Column District[District] appears in two tables; (1) District table, and (2) expanded Store table.
This is not correct there are two district columns District[District] and Store[District]
So, it is incorrect to assume that DISTINCTCOUNT( District[District]) will operate on District table; it has the option of operating on either of the above tables.
No it is not. You are explicity using the District column from the District table
If you have a look back at my original answer I suggested changing your calcuation to DISTINCTCOUNT( Store[District]) so that it would use the District from the Store table instead of using the column from the District table in order to get it working the way you wanted.
- SuraMan
So, it is incorrect to assume that DISTINCTCOUNT( District[District]) will operate on District table; it has the option of operating on either of the above tables.
No it is not. You are explicity using the District column from the District table
This is why I think that a successful explanation needs to explain both "First visualisation" and "Second visualisation" consistently. Both the first and second visualisations explicitly use District[District] reference, but Power BI chooses the expanded Store table and populates column District[District] from the expanded Store table for the first visual and it doesn't do so in the second visualisation.
Just because there is a preceeding "District" in "District[District]" does not mean it refers to District table. That is not how table expansion works. Please see the URL below and observe that TopSellerProduct[Product] is also a part of the expanded Product table.
https://www.sqlbi.com/articles/expanded-tables-in-dax/
PS:
I have read Kimball's and Chris Adamson's books on Dimensional Modelling and I know workarounds to get this to work in the way I expect. This is a purposefully constructed example to highlight what I think an inconsistency that I think needs deeper technical explanation from those who understand the internals.