Forum Discussion

jbolivar's avatar
jbolivar
Frequent Visitor
7 years ago
Solved

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

 

 

Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    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

  • AkhilAshok's avatar
    AkhilAshok
    Solution Sage
    7 years ago

    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.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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