Forum Discussion

turcelaygoy's avatar
turcelaygoy
Icon for Helper I rankHelper I
3 years ago

What happens with the [Value] variable inside two different GENERATESERIES

Quality variation_COCHE2 = 
AVERAGEX ( 
    CROSSJOIN ( 
        VALUES ( 'Tabla Proyecto'[PROYECTO] ),VALUES('Tabla Area'[AREA]) ), 
    VAR T = 
        CALCULATETABLE ( Hoja1 ) 
    RETURN 
        AVERAGEX ( 
           GENERATESERIES ( MIN(Hoja1[ORDEN COCHE PROYECTO]), MAX(Hoja1[ORDEN COCHE PROYECTO]) -1 , 1 ), 
           var Z= [Value] 
               AVERAGEX( 
                  GENERATESERIES ( Z , MAX(Hoja1[ORDEN COCHE PROYECTO]) - 1 , 1 ),
                    VAR W = [Value] 
                    VAR CurrentFaults = SUMX ( FILTER ( T, Hoja1[ORDEN COCHE PROYECTO]= Z), Hoja1[FALTAS TOTALES (QA)] )
                    VAR NextFaults = SUMX ( FILTER ( T, Hoja1[ORDEN COCHE PROYECTO]= W + 1 ), Hoja1[FALTAS TOTALES (QA)] ) 
    RETURN 
         DIVIDE ( NextFaults - CurrentFaults, CurrentFaults ) * 100 )
 ) 
)

Hello everyone, I was trying to calculate this measure but I realised that I need to calculate two GENERATESERIES functions one inside the other. I would like to know if there is any option of making the variables Z and W work. Right now the measure doesn't work due to those two variables. I would be really grateful if anyone has a possible solution for this problem. Thanks a lot in advance ;)!!

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    HI, turcelaygoy 

     

    Your usage of GENERATESERIES function looks fine but the issue may be with the variable scopes. In DAX, a variable maintains its value only within the context of the specific expression where it's defined (in this case, the context of the GENERATESERIES function). That means the variable Z is not recognized by the second GENERATESERIES function.

     

    reorganised code:

     

    Quality variation_COCHE2 =
    AVERAGEX (
    CROSSJOIN (
    VALUES ( 'Tabla Proyecto'[PROYECTO] ),
    VALUES ( 'Tabla Area'[AREA] )
    ),
    VAR T = CALCULATETABLE ( Hoja1 )
    VAR MinOrder = MIN(Hoja1[ORDEN COCHE PROYECTO])
    VAR MaxOrder = MAX(Hoja1[ORDEN COCHE PROYECTO])
    RETURN
    AVERAGEX (
    GENERATESERIES ( MinOrder, MaxOrder -1 , 1 ),
    VAR Z = [Value]
    RETURN
    AVERAGEX(
    GENERATESERIES ( Z , MaxOrder - 1 , 1 ),
    VAR W = [Value]
    VAR CurrentFaults = SUMX ( FILTER ( T, Hoja1[ORDEN COCHE PROYECTO] = Z ), Hoja1[FALTAS TOTALES (QA)] )
    VAR NextFaults = SUMX ( FILTER ( T, Hoja1[ORDEN COCHE PROYECTO] = W + 1 ), Hoja1[FALTAS TOTALES (QA)] )
    RETURN
    DIVIDE ( NextFaults - CurrentFaults, CurrentFaults ) * 100
    )
    )
    )

     

     

    See if it works. 

    If my post helps you in any way, hit 👍. 

     

    • turcelaygoy's avatar
      turcelaygoy
      Icon for Helper I rankHelper I
      3 years ago

      And if I want to do a nested loop how should I do it?

      • rubayatyasmin's avatar
        rubayatyasmin
        Icon for Community Champion rankCommunity Champion
        3 years ago

        try removing the min order max order variables. Then the calculation of NextFaults should be nested inside generateseries where w is defined. 

         

        Quality variation_COCHE2 =
        AVERAGEX (
        CROSSJOIN (
        VALUES ( 'Tabla Proyecto'[PROYECTO] ),VALUES('Tabla Area'[AREA]) ),
        VAR T =
        CALCULATETABLE ( Hoja1 )
        RETURN
        AVERAGEX (
        GENERATESERIES ( MIN(Hoja1[ORDEN COCHE PROYECTO]), MAX(Hoja1[ORDEN COCHE PROYECTO]) -1 , 1 ),
        VAR Z = [Value]
        VAR NextFaults =
        AVERAGEX(
        GENERATESERIES(Z, MAX(Hoja1[ORDEN COCHE PROYECTO]) - 1, 1),
        VAR W = [Value]
        RETURN SUMX ( FILTER ( T, Hoja1[ORDEN COCHE PROYECTO]= W + 1 ), Hoja1[FALTAS TOTALES (QA)])
        )
        VAR CurrentFaults = SUMX ( FILTER ( T, Hoja1[ORDEN COCHE PROYECTO]= Z), Hoja1[FALTAS TOTALES (QA)])
        RETURN DIVIDE(NextFaults - CurrentFaults, CurrentFaults) * 100
        )
        )

         

        see if it works. 

         

        If my assistance helps you in any way, hit 👍.