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 wondering if there is any way of programing loops so I can run the database and see the acumulated variation that is happening through those units of products. 

 

Example:

Right now I'm able to calculate the variation between units 5 and 10 doing: (Number of faults in unit 10-Number of faults in unit 5)/Number of faults in unit 5*100

 

But I would like to calculate the variation taking into account all the units of products that are between 5 and 10; 5,6,7,8,9 and 10.

 

I hope someone can help me with this problem 🙂

 

Thank you!

  • 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
        )
)

23 Replies

  • 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
                )
        )
)

       

       

  • turcelaygoy's avatar
    turcelaygoy
    Helper I
    3 years ago

    It works but the variation that it ruturns is not the one according to the numbers selected. It returns the variation between the initial unit and the (final unit-1).

  • ppm1's avatar
    ppm1
    Solution 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

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      turcelaygoy 
      Thsnk you! Now the picture is more clear. However, you still did not expalin what is the problem. What do you mean by "It returns the variation between the initial unit and the (final unit-1)."? Please elaborate on this with one example and advise what should be the correct result.

      • turcelaygoy's avatar
        turcelaygoy
        Helper I
        3 years ago

        I have chosen project Oslo and initial UT 30 and final UT 32 and it returns me the variation between UT 30 and 33 that is 655% but it should say -67% (these results have been calculated filtering the variables in the excel sheet of the database). But then I have tried different projects and the variation that it returns is not correct. So right now the measure gives a result but not correct.

  • devanshi's avatar
    devanshi
    Helper V
    3 years ago

    VariationBetweenUnits = VAR StartUnit = 5
    VAR EndUnit = 10
    VAR NumUnits = EndUnit - StartUnit + 1
    VAR StartFaults = CALCULATE(SUM('Table'[Number of Faults]), 'Table'[Unit] = StartUnit)
    VAR EndFaults = CALCULATE(SUM('Table'[Number of Faults]), 'Table'[Unit] = EndUnit)
    VAR VariationSum = SUMX( GENERATESERIES(StartUnit, EndUnit-1, 1),
                                                   ABS(CALCULATE(SUM('Table'[Number of Faults]),
                                      'Table'[Unit] = EARLIER(StartUnit)) - CALCULATE(SUM('Table'[Number of Faults]),
                                                       'Table'[Unit] = EARLIER(StartUnit) + 1)) )
    RETURN
    DIVIDE(VariationSum, StartFaults) * 100