cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## 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

1 ACCEPTED SOLUTION
Super User

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))

2 REPLIES 2
Super User

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))

Resolver I

Thank a lot,

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

philippe

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors