Forum Discussion

turcelaygoy's avatar
turcelaygoy
Helper 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
        )
)
turcelaygoy's avatar
turcelaygoy
Helper I
3 years ago

Ok. This is the measure I'm using right now to calculate the variation:

First of all I take the values of the UT from a table that lists all the possible units. Then I do the same with the project and I calculate the variation as follows:

Filtered variation = Var UT= SELECTEDVALUE('inicial unit'[UT]) Var UT2=SELECTEDVALUE('final unit'[UT]) Var Project=SELECTEDVALUE('Project'[PROJECT] )
Return CALCULATE(DIVIDE((CALCULATE(SUMX(Sheet1,Sheet1[TOTAL FAULTS (QA)]),Sheet1[UT]=UT2,Sheet1[PROJECT]=Project)-(CALCULATE(SUMX(Sheet1,Sheet1[TOTAL FAULTS (QA)]),Sheet1[UT]=UT,Sheet1[PROJECT]=Project))),(CALCULATE(SUMX(Sheet1,Sheet1[TOTAL FAULTS (QA)]),Sheet1[UT]=UT,Sheet1[PROJECT]=Project)))*100)
 
But what I would like to calculate is the variation between all the units that are included between the given variables.
Example: If I choose units 3 an 6 I would like to calculate:
(Variation 3-4+Variation 4-5+Variation 5-6)/3
So I would need a loop to change the variable UT that I'm using in the current measure. 
 
I don't know if this example works or you still need more information. Feel free to ask for more info or examples.
tamerj1's avatar
tamerj1
Community Champion
3 years ago

turcelaygoy 
Assumptions:

  • Both 'inicial unit' and 'final unit' tables are disconnected
  • 'Project' table is connected to 'Sheet1' table (1-* single direction)

Please try

Filtered variation =
AVERAGEX (
    CROSSJOIN (
        CROSSJOIN ( VALUES ( 'inicial unit'[UT] ), VALUES ( 'final unit'[UT] ) ),
        VALUES ( 'Project'[PROJECT] )
    ),
    VAR T =
        CALCULATETABLE ( Sheet1 )
    RETURN
        AVERAGEX (
            GENERATESERIES ( 'inicial unit'[UT], 'final unit'[UT] ),
            VAR CurrentFaults =
                SUMX ( FILTER ( T, Sheet1[UT] = [Value] ), Sheet1[TOTAL FAULTS (QA)] )
            VAR NextFaults =
                SUMX ( FILTER ( T, Sheet1[UT] = [Value] + 1 ), Sheet1[TOTAL FAULTS (QA)] )
            RETURN
                IF (
                    NextFaults <> BLANK (),
                    DIVIDE ( NextFaults - CurrentFaults, CurrentFaults ) * 100
                )
        )
)