topic Re: Different results of All and Summarize function on a column both combined with Keepfilters in DAX Commands and Tips

Different results of All and Summarize function on a column both combined with Keepfilters

ARU_ — Mon, 20 Mar 2023 05:59:38 GMT

Hello, I am new to DAX and have recently completed the evaluation concepts.

I am wondering why following yield different result from the other function.

Here is the link to access pbix on which i am working on.

https://drive.google.com/file/d/17uSqXKk-zgmFlRF9XLDnYpsIJZQxhemt/view?usp=sharing

DAX 1

DAX 2

I just replaced All filter with Summarize function. I understand that when All function is used with a column reference then it yields unique values of the column. While Summarize function (when applied on a column) provides unique values. Both these functions being filter parameters of Calculate functions work in original filter context environment.

The way i understand keepfilters works, it intersect the values of columns in original filter context and the values derived of the same column in keepfilters function. Ideally, i was expecting them to yield the same result. However, in this case as we see, it is yielding different output.

Thank you for your time in going through the question and helping me out !

Re: Different results of All and Summarize function on a column both combined with Keepfilters

wdx223_Daniel — Mon, 20 Mar 2023 06:56:31 GMT

with your code, the results will be same with KEEPFILTERS or not. all the outside filters has been removed by ALL(Sales). DAX1, ALL(Customer[Education]) is useless, its filter range is overlaped by ALL(). but DAX2, SUMMARIZE(...) or VALUES(...) refilter the data with Educations, just remove the filter of Fiscal Year, result same as this code:

DAX2 = DIVIDE (

[Sales Amount],

CALCULATE(

[Sales Amount],

ALL('Date'[Fiscal Year]) ))

Re: Different results of All and Summarize function on a column both combined with Keepfilters

tamerj1 — Mon, 20 Mar 2023 07:22:10 GMT

Hi ARU_
Basically in the first one you are removing the filter "ALL ( Customer[Education] )" and the keeping it "KEEPFILTERS" so it is basically doing nothing. The remaining ALL ( Sales ) will remove all filters from 'Sales'

The second one by wrapping SUMMARIZE with KEEPFILTERS nothing will change as SUMMARIZE is already carrying the outer fiilter along with it "which also doesn't make sense". The SUMMARIZE by one column is just the VALUES of that column. Now, ALL ( 'Sales' ) will be intersected with VALUES ( Customer[Education] ) and all filters from 'Sales' are removed and the filter from Customer[Education] is kept.

Note that Customer[Education] column is part of the expaned Sales table.

Re: Different results of All and Summarize function on a column both combined with Keepfilters

ARU_ — Mon, 20 Mar 2023 08:10:58 GMT

Hi tamerj1 , Thanks for ur explanation.

I understand that removing keepfilters / all is redundant in DAX1. The thing which i fail to understand is why it is so.

Let me explain how i think DAX is evaluating this line of code :

keepfilters(all(Customer[Education])

I am taking blue cell in the below screenshot to explain the DAX evaluation steps that i understand.

Step 1 : "All" being an inner function will first be evaluated which will give you distinct values of customer[Education] column. It basically returns a table with only one column containing distinct values

Step 2: keepfilters function accepts table as an expression. Step 1 returns a table expression containing all distinct values of customer[Education] column. As keepfilters is being used in Calculate , it will make use of these values to be intersected with original context values of the same column i.e Customer[Education].

Step 3: As we have customer[Education] on rows in the matrix, the original context for blue cell in the matrix has 'Bachelors' filter applied (which is the original context).

Step 4 : The intersection of Step 3 ('Bachelors') and Step 2 (All values of Customer[Education]) should return 'Bachelors' filter to be applied on that cell.

However, instead of applying filter on 'Bachelors' education only, it is removing filter from customer[education] column. Not sure why ? 😞

I know that this is how the DAX is not computing and hence its results are different from the above steps. I dont understand what piece of puzzle am i missing here.

Again, Thank you so much for attempting to help here.

P.s - For the sake of simplicity and providing exactly where my confusion lies, i am not giving much important to the filter on sales table.

Re: Different results of All and Summarize function on a column both combined with Keepfilters

ARU_ — Mon, 20 Mar 2023 08:22:57 GMT

Hi wdx223_Daniel - Thanks for your explanation. I have no concern about DAX2. The reason i provided it is because i expected DAX 1 to have the same results as DAX2.

You say "All" overlaps the filter of Customer[Education] which i get. The thing which i don't understand why does it overlap?

As per the evaluation steps provided in the response to tamerj1, Keepfilters behaviour is to intersect the original context of a column with the new context as provided by keepfilters.

Hence, DAX ideally shouldnt be removing / overriding the filter coming from the original context as is happening in this case.

Thanks for your time in helping me.

Re: Different results of All and Summarize function on a column both combined with Keepfilters

tamerj1 — Mon, 20 Mar 2023 08:41:33 GMT

ARU_

The problem is with your understanding of step1. Here ALL is not a filter expression rather it is a calculate modifier exactly as REMOVEFILTERS. Actually REMOFILTERS was later introduced just to clear this confusion about the double face of ALL.

You think that ALL ( Customer[Education] ) translates into

FILTER ( ALL ( Customer[Education] ), TRUE ( ) )

but this is not true. It actually translates to

REMOVEFILTERS ( Customer[Education] )

Therefore your argument is

KEEPFILTERS (REMOVEFILTERS ( Customer[Education] ) )

which means nothing

Re: Different results of All and Summarize function on a column both combined with Keepfilters

ARU_ — Mon, 20 Mar 2023 10:05:08 GMT

Thanks tamerj1 for your lucid explanation. It makes sense to me now!

Cheers!