Forum Discussion
Helper IWhat happens with the [Value] variable inside two different GENERATESERIES
Community ChampionHI, 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 👍.
turcelaygoyAnd if I want to do a nested loop how should I do it?
- rubayatyasmin
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 👍.