cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
d_gosbell Senior Member
Senior Member

Re: Single crossfiltering ignored for columns?



 

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

Re: Single crossfiltering ignored for columns?

 

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.

SuraMan Regular Visitor
Regular Visitor

Re: Single crossfiltering ignored for columns?

Hi @v-jiascu-msft 

 

The last two posts I posted in this thread were marked by "SPAM" by someone, and as a result, they were removed.

Can you please investigate this abusive behaviour of marking legitimate posts as "spam" to remove them and take action against those who did it? Can you also restore my posts that were removed?

 

Thanks

 

d_gosbell Senior Member
Senior Member

Re: Single crossfiltering ignored for columns?

Hmm, I did wonder where that reply went. Smiley Happy  I got the text below in the email notification of your response:

 

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 uses District[District] reference, but Power BI chooses the expanded Store table and populate District[District] from it for the first visual and it doesn't do so in the second visual. 



 

So "Table Expansion" is not a phyical thing, it's a conceptual tool used to help explain certain behaviours. In this case I think it's just confusing the issue.

 

With standard single direction relationships, tables on the "one" side filter tables on the "many" side (so District filters Store)

 

But tables on the "many" side DO NOT filter tables on the "one" side (so Stores does not filter Disticts)

 

In Visual 1 the order of the columns on the visual is irrelevant, because of the relationships in the model District[District] is filtering Store[Chain]. One conceptual way of thinking of this is that the engine evaluates all the combinations of Store[Chain] and District[District] and it finds that District[District] is in the extended tables for Store so it uses the current District[District] to filter records from the Stores table.

 

But in Visual 2 we have a set of rows from Store[Chain] to calculate a measure we have to pass the values from Store[Chain] as a filter context to the column that is referenced in the  distinctcount. In order to apply the filter context it looks through the extended tables associated with the District table to see if Store[Chain] can be reached, but because Store is on the "many" side of the relationship the Store table is not part of the extended tables for the District table. Each table will have a different set of extended tables, notice in Alberto's article that Sales expands to Product and TopSellerProduct, but Product only expands to TopSellerProduct .

 

 

Just because there is a preceeding "District" in "District[District]" does not mean it refers to District table.


This is not correct. The preceeding district does mean that we are explicitly referencing a column in the District table. 

 

 

 

Community Support Team
Community Support Team

Re: Single crossfiltering ignored for columns?

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,

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?

Just because there is a preceding "District" in "District[District]" does not mean it refers to District table.


This is not correct. The preceeding district does mean that we are explicitly referencing a column in the District table. 

 


 

If you observe Alberto's example, Product[Product] appears in all 3 expanded tables and not limited to Product table. Just because there was a preceding Product in Product[Product] did not prevent that column being present in all 3 expanded tables.

In Visual 1 the order of the columns on the visual is irrelevant, because of the relationships in the model District[District] is filtering Store[Chain].

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.

 

What 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 as a related column.

 

Now to summarise;

Both First visual and Second visual uses the same two columns only. Store[Chain] and District[District]. Nothing else.

expanded Store table contains both of the above columns.

So, both visuals had the option of choosing the expanded Store table.

 

Clearly, the first visual chose it, but the second visual did not. Second visual could also have easily chosen the expanded Store table because both the required columns (native column Store[Chain] and related column District[District]) are in it, so why it refused to chose the expanded Store table needs a consistent explanation.

 

 

d_gosbell Senior Member
Senior Member

Re: Single crossfiltering ignored for columns?


What 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)

SuraMan Regular Visitor
Regular Visitor

Re: Single crossfiltering ignored for columns?

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.

 

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

Re: Single crossfiltering ignored for columns?



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.