Forum Discussion

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

Possible loop in DAX

Hello!   I was trying to calculate the variation of some faults in the different units of products and I have been able to see the variation between to independent units of products. I was wonderin...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi turcelaygoy 
    I think now is correct.

    Filtered variation_2 = 
AVERAGEX (
    CROSSJOIN (
        CROSSJOIN ( VALUES ( 'UT_INII'[UT1] ), VALUES ( 'UT_FIN'[UT2] ) ),
        VALUES ( 'Tabla Proyecto'[PROYECTO] )
    ),
    VAR T =
        CALCULATETABLE ( Hoja1 )
    RETURN
        AVERAGEX (
            GENERATESERIES (  [UT1], [UT2] - 1, 1 ),
            VAR CurrentFaults =
                SUMX ( FILTER ( T, Hoja1[Columna] = [Value] ), Hoja1[FALTAS TOTALES (QA)] )
            VAR NextFaults =
                SUMX ( FILTER ( T, Hoja1[Columna] = [Value] + 1 ), Hoja1[FALTAS TOTALES (QA)] )
            RETURN
                DIVIDE ( NextFaults - CurrentFaults, CurrentFaults ) * 100
        )
)
ppm1's avatar
ppm1
Icon for Solution Sage rankSolution Sage
3 years ago

Have you tried to create a table variable in a measure? For example,

 

VAR UnitTable = ADDCOLUMNS(VALUES(Table[Unit]), "YourMeasure", [YourMeasure])

RETURN Min, Max, Median, Avg calculation

 

If you want to specify only a fixed subset of units, you can wrap the ADDCOLUMNS expression with CALCULATETABLE(ADDCOLUMNS(...), Table[Unit] IN {5,6,7,8,9,10} )

 

Pat