Skip to main content
cancel
Showing results for 
Search instead 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

Reply
PhilippeMuniesa
Resolver I
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

 

nomBrutAnnée 
Titi1125,452020 
Toto3456,872021 
Tata3456,212020 
tete1765,122021 

 

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
FreemanZ
Super User
Super User

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:

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

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:

Thank a lot,

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

philippe

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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