Possible loop in DAX

[turcelaygoy]

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!

[devanshi]

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

[turcelaygoy]

nlka

[tamerj1]

@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]

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.

[tamerj1]

@turcelaygoy 

Can you share a sample file?

[turcelaygoy]

of the excel sheet or the power BI

[tamerj1]

Power Bi

[turcelaygoy]

It doesn't let me share the file

[tamerj1]

@turcelaygoy 
You can only share a download link.

[turcelaygoy]

Link

[turcelaygoy]

Does this link work? @tamerj1 

[tamerj1]

@turcelaygoy 

Yes it worked and I have downloaded the file but still didn't have the chance to look at it. 

[tamerj1]

@turcelaygoy 

So sorry. I was so busy with other stuff and didn't have the chance to look into it. Today I'm out of office. I will check on Monday morning. 

[turcelaygoy]

No problem!!

[tamerj1 · Accepted solution]

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]

Hello @tamerj1 !!!

I have another doubt ;). I would like to see the variation of the faults between the different "Coches"(it means cars in Spanish) of each project. I want to have the choice of the variables "Proyecto" and "Area" in two different data segmentations and see the variation of the whole project but calculating first the variation in each "Coche" and then doing the average of all.

To do this, I need to first order the "Coches" that have been produced. I have thought of doing a new column setting numbers in order to the first date of inspection "Fecha 1ª Inspeccion" of each "Coches", but I cannot manage to create that column.

After this I would do the variation of the faults in each car using the code you provided and after that the average of all the variations of the different "Coches".

[turcelaygoy]

Thank you very much!!

[ppm1]

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

[turcelaygoy]

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

[tamerj1]

@turcelaygoy 
I believe I need to see some screenshots.