topic Calculate filter, with format or value in DAX Commands and Tips

Calculate filter, with format or value

PhilippeMuniesa — Tue, 20 Dec 2022 15:08:44 GMT

Hello,

In a powerPivot table. "CompletContratrem"

I have a "Brut" column, decimal number format.
And a measure "fx_Brut":=Sum([Brut])

and another "Année" column, Text format

example

nom	Brut	Année
Titi	1125,45	2020
Toto	3456,87	2021
Tata	3456,21	2020
tete	1765,12	2021

I would like the "Brut" total if "Année" = 2020,

I tried several solutions, but it stuck

I have already calculated a measure to get 2020 (the smallest year in the Year column)

Regul_N_1:=calculate(value(max(CompletContratrem[Année]))-1)

i get good 2020

But,

even though I converted the "Année" column to integer, the metric "fx_Brut_N_1"

fx_Brut_N_1:=calculate([fx_Brut];filter(CompletContratrem;CompletContratrem[Année] =[Regul_N_1]))

, returns me (empty)

If I leave the Year column as text, and I adapt the measure by converting Année with Value

fx_Brut_N_1:=calculate([fx_Brut];filter(CompletContratrem;Value(CompletContratrem[Année] )=[Regul_N_1]))

, I also have a value return (empty)

On the other hand, if I modify the measure and set a hard comparison criterion ex: 2020, it works.

fx_Brut_N_1:=calculate([fx_Brut];filter(CompletContratrem;Value(CompletContratrem[Année] )=2020))

, returns the correct value

but,

fx_Brut_N_1:=calculate([fx_Brut];filter(CompletContratrem;Value(CompletContratrem[Année] )=value([Regul_N_1])))
Always returns (empty)

I don't understand, I need explanations and help.

Thank for your help

Philippe

Re: Calculate filter, with format or value

FreemanZ — Tue, 20 Dec 2022 21:27:44 GMT

hi PhilippeMuniesa

because there is contex transition. when you call for the measure inside the filter function, filter provides a row context and the implicite CALCULATE surrounding the measure convert the row context to a filter context. it compares 2021 vs 2021-1, and 2020 vs 2020-1, so blank is returned.

This shall work:

fx_Brut_N_1:=

VAR _value = [Regul_N_1]

RETURN calculate([fx_Brut];filter(CompletContratrem;Value(CompletContratrem[Année] )=_value))

More info about context transition:

https://www.sqlbi.com/articles/understanding-context-transition/#:~:text=The%20context%20transition%20in%20DAX%20is%20the%20transformation,once%20you%20understand%20row%20context%20and%20filter%20context.

Re: Calculate filter, with format or value

PhilippeMuniesa — Thu, 22 Dec 2022 06:18:37 GMT

Thank a lot,

i will read the article on context transition that i don't know.

philippe