Hello, I understand that when relationships are set to Single Direction Crossfiltering, filtering from the many side does not flow to the one side. For example, in the attached data model, filters on Store should not flow to District. This PBIX was taken from https://docs.microsoft.com/en-us/power-bi/sample-retail-analysis. Then the question is, why the two visualisations show conflicting values. First visualisation table: Store.[Chain], District.[District]Second visualisation table: Store.[Chain], DISTINCTCOUNT( District.[District]) 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]? Single direction crossfiltering

Hi SuraMan , I have removed them from the Spams. This is a system function without human intervention. Please feel free to @me if you have such issues. Best Regards,

> 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.

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

> 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

Hi SuraMan , Could you please mark the proper answers as solutions? Best Regards,

Single crossfiltering ignored for columns? | Microsoft Fabric Community

18 Replies

d_gosbell
Super User
7 years ago
> 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.
- SuraMan
  Advocate II
  7 years ago
  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
    Super User
    7 years ago
    > 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
v-jiascu-msft
Microsoft Employee
7 years ago
Hi SuraMan ,

Could you please mark the proper answers as solutions?

Best Regards,
- SuraMan
  Advocate II
  7 years ago
  v-jiascu-msft wrote:
  Hi SuraMan ,
  
  Could you please mark the proper answers as solutions?
  
  Best Regards,
  
  Hi v-jiascu-msft ,
  
  No, a proper explanation has not been given.
  
  1. Display the below two columns in a table:
  Store.[Chain], District.[District] --> District.[District] is filtered appropriately in each row.
  
  2. Display the below two columns in a table:
  Store.[Chain], DISTINCTCOUNT( District.[District]) --> District.[District] is not filtered.
  
  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?
  
  If you want to know what expanded tables are, please see:
  https://www.sqlbi.com/articles/expanded-tables-in-dax/
  
  Please note that expanded tables are created by ignoring the cross filtering direction.
  
  Thanks
  
  Regards
  - v-jiascu-msft
    Microsoft Employee
    7 years ago
    Hi SuraMan ,
    
    I think the explanation of Expanded tables is too technical. To be honest, the relationship and filtering are the most complex parts in DAX, especially when they are nested.
    
    Some of my thoughts are as follows.
    
    1. There could be two parts of the connection between the two tables. One is the relationship, which connects two tables. The other one is filtering.
    
    2. Actually, both the two fields are the context in the first visual. They are just connected by the relationship.
    
    3. In the second visual, the context is the [Chain]. The filter direction works now. We can ask like "how many districts of each Chain?". But the context [Chain] can't filter the other table. So it counts all the [District] that it connects to.
    
    I hope these above can give you some ideas.
    
    Best Regards,