topic Re: why my calculate not overwriting filter context in DAX Commands and Tips

why my calculate not overwriting filter context

chahineatallah — Mon, 01 Aug 2022 19:05:50 GMT

Hello

As i know calculate will override filter context that is already present, but this is not happening in my case, where i was trying to have sales for certain customers, so when i put customer name as filter in calculate it only shows sales for this particular customer, so its basically acting like keepfilters, or am missing on something? to my understanding it should show the same value on each row of the table as it will overwrite the customer/company name as in my case, but its not working this way

Re: why my calculate not overwriting filter context

AUaero — Mon, 01 Aug 2022 19:24:33 GMT

You haven't removed the filter context for the customer from the current row.

Try this:

CALCULATE( SUM(Orders[_Sales]), ALL(Customers), Customers[CompanyName] = "alfreds futterkiste" )

Re: why my calculate not overwriting filter context

chahineatallah — Tue, 02 Aug 2022 03:44:58 GMT

thanks for reply, but to my understanding calculate should automatically modify the filter context in the row level & overwrite it

i will try today

Re: why my calculate not overwriting filter context

AUaero — Tue, 02 Aug 2022 13:10:29 GMT

You're close - CALCULATE changes the filter context, but doesn't eliminate the existing context.
SQLBI has a good introduction to what CALCULATE actually does:
Introducing CALCULATE in DAX - SQLBI

Re: why my calculate not overwriting filter context

Anonymous — Thu, 04 Aug 2022 09:57:32 GMT

Hi chahineatallah ,

For filters with Boolean conditions('Customers'[CompanyName] = "alfreds futterkiste"), DAX converts it to a list of values. So, whenever you write the code above, DAX turns this expression into:

sales using calculate = CALCULATE ( SUM ( 'Order'[_Sales] ), FILTER ( ALL ( 'Customers'[CompanyName] ), 'Customers'[CompanyName] = "alfreds futterkiste" ) )

Let's go back to this table visual. In each row, DAX creates a filter context containing the corresponding company name. For example, in the Around the Horn row, the original filter context created by the table contains a filter that displays only the company name "Around the Horn". CALCULATE then calculates the filter parameter, which returns a table with only the company name "alfreds futterkiste". The newly created filter overwrites the existing filter on the same column.

You have understood the whole process very well already, that's why you have the question above. So my doubt is whether the [company name] column comes from the customer table.🤔

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Re: why my calculate not overwriting filter context

chahineatallah — Thu, 04 Aug 2022 12:26:31 GMT

Hi Gao

Am using the customername from customer table which is my lookup table (only unique values), this why it confused me :), as to my knowledge & as you explained also, it should over write the filter context which is in each row, but its acting like as if i put keepfilters

Re: why my calculate not overwriting filter context

daXtreme — Thu, 04 Aug 2022 13:36:55 GMT

AUaero

Sadly, you're not correct but chahineatallah is. If, in CALCULATE, you're putting a list of values for a column, then these take priority over whatever comes from outside, effectively overwriting/eliminating the existing context (values). This is exactly how it works (for more details please go to https://dax.guide/calculate). Removal of filters from the column is automatic in this case and these filters are replaced by the values supplied under CALCULATE.

Re: why my calculate not overwriting filter context

daXtreme — Thu, 04 Aug 2022 13:36:29 GMT

Hi chahineatallah

Your understanding is correct. If you don't get the result you're describing, it means something else is at play here. You're probably not showing us everything.

Re: why my calculate not overwriting filter context

chahineatallah — Thu, 04 Aug 2022 16:51:41 GMT

Hello Daxtreme

frankly i couldnt find whats the problem, but am using customer name from lookup, and sales calculate measure, i will post link to my file, just am curious what am doing wrong, as first time i encounter something like that 🙂

power bi file

thanks

Re: why my calculate not overwriting filter context

daXtreme — Thu, 04 Aug 2022 20:49:35 GMT

chahineatallah

I know what's going on... This is the version that works:

sales using calculate = CALCULATE( [Total sales], Customers[CompanyName] = "Wilman Kala", REMOVEFILTERS( Customers[CustomerID] ) // See that? )

And the explanation is that you have done this:

This means you've got 2 identifiers in the table. You should not do such things; a table should only ever have one identifier. Here's the code that the visual generates:

// DAX Query DEFINE VAR __DS0Core = SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL( ROLLUPGROUP('Customers'[CustomerID], 'Customers'[CompanyName]), "IsGrandTotalRowTotal" ), "Total_sales", 'Orders'[Total sales], "sales_using_calculate", 'Orders'[sales using calculate] ) VAR __DS0PrimaryWindowed = TOPN( 502, __DS0Core, [IsGrandTotalRowTotal], 0, [Total_sales], 0, 'Customers'[CompanyName], 1, 'Customers'[CustomerID], 1 ) EVALUATE __DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, [Total_sales] DESC, 'Customers'[CompanyName], 'Customers'[CustomerID]

As you see, it keeps filters on both fields in SUMMARIZECOLUMNS that are marked as identifiers. So then, overwriting the filter on one column is not enough. The lesson to take from that is that one should avoid creating more than 1 ID column in a table.

If you want to necessarily keep both ID fields, then you have to change the measure to:

sales using calculate = var CompanyName = "Wilman Kala" var CompanyNameFilter = CALCULATETABLE( SUMMARIZE( Customers, Customers[CustomerID], Customers[CompanyName] ), REMOVEFILTERS( Customers[CompanyName], Customers[CustomerID] ), Customers[CompanyName] = CompanyName ) var Result = CALCULATE( [Total sales], CompanyNameFilter ) return Result

This is because you have to get rid of filters on both the ID columns.

Or even better:

sales using calculate = CALCULATE( [Total sales], // Needed because of the two ID's in the table REMOVEFILTERS( Customers ), Customers[CompanyName] = "Wilman Kala" )

Re: why my calculate not overwriting filter context

chahineatallah — Fri, 05 Aug 2022 03:57:33 GMT

hi Daxtreme

frankly i didnt know what are these icons, but how come i have 2 identifiers, in this customer table there are only unique items no duplications, also my relationship is 1 to many, this table i got it from a dax challenge by curbal as am learning dax (i want to learn it in deep detail, its great), so i didnt know how these icons came, i though first maybe its some category issue, as i used to get these icons with countries/dates/areas etc, but never with names/text

i didnt get your explanation about the identifiers & this dax query (how did you do that 🙂 ), if you have any article about this please share as its new to me

and finally how to make only one identifier (1 icon only), this is new to me about identifiers

Re: why my calculate not overwriting filter context

daXtreme — Fri, 05 Aug 2022 07:54:49 GMT

Hi there.

I don't know about any articles about such things. I have learnt the quirks of DAX and PBI mostly from Alberto Ferrari's and Marco Russo's videos on YT. Apart from the fact that I've been through their courses and read their Book several times. And have done countless exercises and no end of troubleshooting on other people's models.

It's what you call EXPERIENCE, I guess.

Re: why my calculate not overwriting filter context

chahineatallah — Fri, 05 Aug 2022 12:58:12 GMT

hello

nice, am also reading book "definitive guide to dax" reached chapter 8 till now (this is the book ur referring to i guess")

can u tell me how to make only one identifier?

thanks

Re: why my calculate not overwriting filter context

daXtreme — Fri, 05 Aug 2022 14:00:47 GMT

When you select a table, play with the Row Label and Key Column dropdowns. If you have 2 different columns in there, they will both be marked as ID's for the table. So, it's better to have one field that will serve both purposes or just not to fill in both, only one of them.

Re: why my calculate not overwriting filter context

r-e-jackson — Wed, 08 May 2024 09:32:33 GMT

Hi, I have had a similar situation and have found that if the column shown in a table is sorted by another column, then the value only shows in the row that matches the filter in the calculate function. When I removed the sorting by another column, then the same calculate function populated all rows. What I found was that the measure filter must use the field name that the column is sorted by (and not just the column that you are showing in the visual). Hope this helps!