cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## 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!

1 ACCEPTED SOLUTION
Super User

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 23
Helper V

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

Helper I

nlka

Super User

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

Helper I

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.

Super User

Can you share a sample file?

Helper I

of the excel sheet or the power BI

Super User

Power Bi

Helper I

It doesn't let me share the file

Super User

@turcelaygoy

Helper I

Helper I

Super User

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

Super User

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.

Helper I

No problem!!

Super User

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
)
)``````
Helper I

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

Helper I

Thank you very much!!

Solution Sage

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

Microsoft Employee
Helper I

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

Super User

@turcelaygoy
I believe I need to see some screenshots.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors