cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jbolivar Frequent Visitor
Frequent Visitor

Parameter What If

Hello friends.

According to the function indicated below, a calculation is being made to a specific year (2019).

 

AcumulaPeriodos Unds = //
VAR
CuentaMeses = CALCULATE(DISTINCTCOUNT(DATA[Mes]);DATA[AÑO FISCAL]="2019")
VAR
DiferenciaMeses= 12 - CuentaMeses
RETURN
IF(DATA[AñoMax]="2019";CALCULATE([mTOTALVENTAS Und.];DATA[AÑO FISCAL]="2019");CALCULATE(SUM(DATA[Unidades]);PARALLELPERIOD(DATA[Fecha];-DiferenciaMeses;MONTH)))

 

 

I need to replace that year through a parameter, which I have already created. But when I replace it I get the following error

 

1.- When I place the parameter in the function Calculate:
A function 'CALCULATE' has been used in a True / False expression that is used as a table filter expression. This is not allowed.

 

2.- When I put it in the IF function:

DAX comparison operations do not support comparing values of the type TEXT with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the value.

 

I do not know what function to use to change the value from Numeric type to Text type.

 

Thank you

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AkhilAshok Established Member
Established Member

Re: Parameter What If

You can create a variable in the starting of the measure and use this variable everywhere:

VAR SelectedYear = FORMAT(SELECTEDVALUE(Paremeter[Paramter]), "####")

 

Format function will convert the Integer to a Text.

View solution in original post

2 REPLIES 2
AkhilAshok Established Member
Established Member

Re: Parameter What If

You can create a variable in the starting of the measure and use this variable everywhere:

VAR SelectedYear = FORMAT(SELECTEDVALUE(Paremeter[Paramter]), "####")

 

Format function will convert the Integer to a Text.

View solution in original post

Community Support Team
Community Support Team

Re: Parameter What If

Hi  @jbolivar 

I test on my side, create a what if parameter to add whole number from -10 to 5 to the specific year 2019.

From your post, it seems the columns DATA[AÑO FISCAL] and [AñoMax] are set to type Text. 

 

If you could accept to transform them to type Number, the function below works 

 

AcumulaPeriodos Unds = 
VAR CuentaMeses = CALCULATE(DISTINCTCOUNT(DATA[Mes]),
FILTER(DATA,DATA[AÑO FISCAL]=2019+[Parameter Value]))
VAR DiferenciaMeses= 12-CuentaMeses
RETURN IF(MAX([AñoMax])=2019+[Parameter Value],CALCULATE([mTOTALVENTAS Und.],
FILTER(DATA,DATA[AÑO FISCAL]=2019+[Parameter Value])),
CALCULATE(SUM(DATA[Unidades]),PARALLELPERIOD(DATA[Fecha],-DiferenciaMeses,MONTH)))

 

If you need keep the columns DATA[AÑO FISCAL] and [AñoMax] type Text, functions below works

 

Measure = FORMAT(2019+[Parameter Value],"General Number")

AcumulaPeriodos Unds =
VAR CuentaMeses = CALCULATE(DISTINCTCOUNT(DATA[Mes]),
FILTER(DATA,DATA[AÑO FISCAL]=[Measure]))
VAR DiferenciaMeses= 12-CuentaMeses
RETURN IF(MAX([AñoMax])=[Measure],CALCULATE([mTOTALVENTAS Und.],
FILTER(DATA,DATA[AÑO FISCAL]=[Measure])),
CALCULATE(SUM(DATA[Unidades]),PARALLELPERIOD(DATA[Fecha],-DiferenciaMeses,MONTH)))

 

 

Best Regards

Maggie

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 44 members 983 guests
Please welcome our newest community members: