topic What happens with the [Value] variable inside two different GENERATESERIES in DAX Commands and Tips

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

turcelaygoy — Mon, 17 Jul 2023 09:57:50 GMT

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 ;)!!

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

rubayatyasmin — Mon, 17 Jul 2023 10:11:50 GMT

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 👍.

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

turcelaygoy — Mon, 17 Jul 2023 10:27:27 GMT

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

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

rubayatyasmin — Mon, 17 Jul 2023 10:44:48 GMT

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 👍.