Forum Discussion
Calculate filter, with format or value
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
- 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:
2 Replies
- FreemanZSuper Userbecause 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:
- PhilippeMuniesaResolver I
Thank a lot,
i will read the article on context transition that i don't know.
philippe