Forum Discussion
Advocate IISingle crossfiltering ignored for columns?
Super User> Why is the first visualisation table filtering appropriate District.[District]s, but when changed it to aggragate Count(Distinct), filtering doesn't flow to District.[District]?
In your first visual Chain is not actually filtering District, the District is filtering the Chains (the order on the visual is not important, this is driven by the relationships between the tables)
One possible "fix" for this is to not create a measure on a table on the "one" side of a relationship. If you did DISTINCTCOUNT( Store[DistrictID] ) I think you will get the result you are expecting.
Advocate II
d_gosbell wrote:
In your first visual Chain is not actually filtering District, the District is filtering the Chains (the order on the visual is not important, this is driven by the relationships between the tables)
Hi d_gosbell,
Thanks for the quick reply.
Is there a rule to find out which tables filters which and when? I understand that we can hypothesise that, in this case, Power BI knows that Store cannot filter District, therefore, it magically decides to go other way and use District to filter Store. Then why does DISTINCTCOUNT(District[District]) not give the filtered output? It looks as if that the measure is evaluated before two tables are joined and filtered for display. You see, these are only our guesses based on what we see. Are there definite rules to explain how these things work?
PS:
I know that DISTINCTCOUNT on Store tables gives the desired output, however, I am using this to highlight what I see as an inconsistency.
Secondly, as a design pattern, is it possible to avoid defining Measures on columns from "one side"? Suppose there is a column in District called [State] and we want the distinct count of [State]?
thanks
- d_gosbell
> Is there a rule to find out which tables filters which and when?
The filtering follows the relationships. See how the arrow on the relationship points from District to Store? This indicates how the filters flow from one table to the other.
>Then why does DISTINCTCOUNT(District[District]) not give the filtered output?
Because your second visual only has an attribute from stores in it. So the District table therefore cannot be filtered, so it returns the count of all district rows for each chain
> Secondly, as a design pattern, is it possible to avoid defining Measures on columns from "one side"?
Yes, if you use dimensional modelling techniques to build your schema this is nearly always possible. A guy called Ralph Kimball wrote a number of excellent books on dimensional modelling, they were written many years ago before PowerBI existed, but the theory in those books is still valid today
> Suppose there is a column in District called [State] and we want the distinct count of [State]?
Typically you don't find business scenarios like this, there is not a lot of value in just counting states in isolation. Typically you'll want a count of distinct customers by state, or total sales amount by state or count of orders by state and in all these scenarios state is used as a grouping column, not as something that you apply an aggregate to