cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SuraMan Regular Visitor
Regular Visitor

Single crossfiltering ignored for columns?

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]?

 

Screen 1 Relationship.PNGSingle direction crossfilteringScreen 2 table.PNG

 

 

18 REPLIES 18
d_gosbell Senior Member
Senior Member

Re: Single crossfiltering ignored for columns?

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 Regular Visitor
Regular Visitor

Re: Single crossfiltering ignored for columns?


@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 Senior Member
Senior Member

Re: Single crossfiltering ignored for columns?

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

Community Support Team
Community Support Team

Re: Single crossfiltering ignored for columns?

Hi @SuraMan ,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SuraMan Regular Visitor
Regular Visitor

Re: Single crossfiltering ignored for columns?


@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

Community Support Team
Community Support Team

Re: Single crossfiltering ignored for columns?

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SuraMan Regular Visitor
Regular Visitor

Re: Single crossfiltering ignored for columns?

Hi @v-jiascu-msft ,

 

Thank you for trying to help.

 

Are you able to get this answered by a Microsoft Power BI expert who understands this behaviour and can explain authoritatively why and how this happens? 

 

Thanks

 

d_gosbell Senior Member
Senior Member

Re: Single crossfiltering ignored for columns?


 

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.

SuraMan Regular Visitor
Regular Visitor

Re: Single crossfiltering ignored for columns?



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.


 

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.