topic Re: Calculate multi column arguments with vs without Filter -understanding internal logic in DAX Commands and Tips

Calculate multi column arguments with vs without Filter -understanding internal logic

dhrubojtg — Sun, 14 Mar 2021 14:08:07 GMT

Dear Dax Gurus

I am learning Dax and need your expert guidance

PBIX reference1:
https://www.dropbox.com/s/6v789rra5rerowo/Contoso.pbix?dl=0

Result screenshot of comparison of both measures

https://www.dropbox.com/s/2snl45p6377bs1u/PBIDesktop_bQTBAWMCC8.png?dl=0

reference2
Sales Amount =

SUMX(

'Online Sales',

'Online Sales'[Sales Quantity] * 'Online Sales'[Unit Price] - 'Online Sales'[Discount Amount]

)

What is the difference between following measures , it seems same but the results are different(Both 1,2 measure are run on same data model )

1.
CalculateWithFilter = CALCULATE(

[Sales Amount],

FILTER(Products,Products[Brand Name] = "Contoso"),

FILTER(Customers,Customers[Country] = "Canada" || Customers[Country] = "China"),

FILTER('Calendar','Calendar'[Is Work Day] = "WorkDay"),

FILTER('Calendar','Calendar'[Calendar Month] = 200701)

)

2.

CalculateWithoutFilter = CALCULATE(

[Sales Amount],

Products[Brand Name] = "Contoso",

Customers[Country] = "Canada" || Customers[Country] = "China",

'Calendar'[Is Work Day] = "WorkDay",

'Calendar'[Calendar Month] = 200701

)

Re: Calculate multi column arguments with vs without Filter -understanding internal logic

AntrikshSharma — Sun, 14 Mar 2021 15:54:01 GMT

dhrubojtg a lot of things in DAX are syntax sugars. So taking the example of 2nd code:

This:

CalculateWithoutFilter = CALCULATE ( [Sales Amount], Products[Brand Name] = "Contoso", Customers[Country] = "Canada" || Customers[Country] = "China", 'Calendar'[Is Work Day] = "WorkDay", 'Calendar'[Calendar Month] = 200701 )

Exands internally and becomes:

CalculateWithoutFilter = CALCULATE ( [Sales Amount], FILTER ( ALL ( Products[Brand Name] ), Products[Brand Name] = "Contoso" ), FILTER ( ALL ( Customers[Country] ), Customers[Country] = "Canada" || Customers[Country] = "China" ), FILTER ( ALL ( 'Calendar'[Is Work Day] ), 'Calendar'[Is Work Day] = "WorkDay" ), FILTER ( ALL ( 'Calendar'[Calendar Month] ), 'Calendar'[Calendar Month] = 200701 ) )

The expansion is clearly visible in the Logical query plan that Formula Engine generates (There are 2 engines in DAX, Storage and Formula Engine):

I also have the Contoso Model so here is how the expansion appers in the query plan generated internally by DAX engine: (If you want to know how to read a query plan check the first link after my signature)

So how is that both codes are different?

The difference is in the way you have referenced Products, Calendar, and Customer tables inside FILTER, when the expansion in previous code happened it became FILTER ( ALL ... which ignores any existing filter on the column/table, but you are writing naked table reference which is getting filtered in the filter context so here is what happens in the with Filter version i.e. FILTER ( Products:

1. There is an outer filter context outside CALCULATE where you have called the measure

2. CALCULATE evaluates the Products table in the outer filter context and once this table is filtered, FILTER evaluates the conditions that you have specified, i.e. Brand = "Contoso"

3. CALCULATE applies this new filter of Brand = Contoso to the filter context in which it evaluates the sales amount

The problem in the above execution is once the products table is filtered in a filter context where Brand is "Fabrikam", the result of FILTER will be an empty table

Here is what happens when you use without FILTER version, i.e. Products[Brand Name] = "Contoso"

1. There is an outer filter context outside CALCULATE where you have called the measure

2. Products[Brand Name] = "Contoso" expands and becomes FILTER ( ALL ( Products[Brand Name] ), Products[Brand Name] = "Contoso" ), the ALL ignores any existing filter context on the same column and always returns all the brands and in this case the result of CALCULATE won't be an empty table and Contoso will be the final filter context for each row of your visual

3. CALCULATE overrides the exisiting filters on Brand column with new filter which is Contoso and evaluates each cell of your visual where the filter context for Brand is Contoso

Now if the result of CalculateWithFilter is what you want then you need to use KEEPFILTERS like this:

CalculateWithoutFilter = CALCULATE ( [Sales Amount], KEEPFILTERS ( Products[Brand Name] = "Contoso" ), KEEPFILTERS ( Customers[Country] = "Canada" || Customers[Country] = "China" ), KEEPFILTERS ( 'Calendar'[Is Work Day] = "WorkDay" ), KEEPFILTERS ( 'Calendar'[Calendar Month] = 200701 ) )

KEEPFILTERS does a SET operations where it simply intersects outer filter and the new filter created by CALCULATE if the values in the outer filter exists in new one then you will get a result otherwise a blank.

Re: Calculate multi column arguments with vs without Filter -understanding internal logic

dhrubojtg — Sun, 14 Mar 2021 17:37:44 GMT

AntrikshSharmaThank you for taking time to reply and help me out .
I tried a lot to give you detailed reply but while posting I am getting HTML error . So I am writing short reply

To make sure i understood , I created another filter which is the expanded version of meaure without All.

Now when i use that in my exting external context, I dont' see the same result the result is very different for withou filter version and the expanded version of without filter version with All

Re: Calculate multi column arguments with vs without Filter -understanding internal logic

AntrikshSharma — Sun, 14 Mar 2021 17:45:35 GMT

dhrubojtg Can you share the code of the new measure.

Re: Calculate multi column arguments with vs without Filter -understanding internal logic

dhrubojtg — Mon, 15 Mar 2021 05:01:43 GMT

It is the expanded version you shared which I expect to give same result as the non expanded form , but when I apply it doesnot

CalculateWithoutFilterAllExp = CALCULATE ( [Sales Amount], FILTER ( ALL ( Products[Brand Name] ), Products[Brand Name] = "Contoso" ), FILTER ( ALL ( Customers[Country] ), Customers[Country] = "Canada" || Customers[Country] = "China" ), FILTER ( ALL ( 'Calendar'[Is Work Day] ), 'Calendar'[Is Work Day] = "WorkDay" ), FILTER ( ALL ( 'Calendar'[Calendar Month] ), 'Calendar'[Calendar Month] = 200701 ) )

Re: Calculate multi column arguments with vs without Filter -understanding internal logic

AntrikshSharma — Mon, 15 Mar 2021 05:15:57 GMT

dhrubojtg You need to add KEEPFILTERS before each FILTER. I have mentioned that in my previous post.

Re: Calculate multi column arguments with vs without Filter -understanding internal logic

dhrubojtg — Mon, 15 Mar 2021 10:48:56 GMT

AntrikshSharma Thank you for the great help ,I actually mistyped my question ,partly because even I was not aware that I mistyped which i know now .
You given right answer , I was not expecting to use keep filters to remove existing context . . but I was expecting same as you noted . .which infact was not happening .

What I did by mistake is -I created a column (And assumed it to be a measure)-and wrote the formula that I would for a measure.
If you see screnshot and also the pbix , I have created CalculateWithoutFilter as Calculated column -and was in wrong assumption that it was a measure.

Thanks for help a lot .