cancel
Showing results for
Did you mean:
Highlighted
jbolivar 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

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

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.

2 REPLIES 2
AkhilAshok 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. Community Support Team

Re: Parameter What If

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

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

Best Regards

Maggie

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. 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. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 44 members 983 guests
Recent signins:
• • smpa01 • tblodget • andrewtlavoie • MLMarshall • • Gordonlilj • Bielite • kylerligon • drewsk • Ashley_Finney • paulocorrea • martinezmachuca • oleg_rod 