cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors