topic Re: Possible loop in DAX in DAX Commands and Tips

Possible loop in DAX

turcelaygoy — Wed, 21 Jun 2023 08:47:28 GMT

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!

Re: Possible loop in DAX

tamerj1 — Wed, 21 Jun 2023 10:22:48 GMT

Hi turcelaygoy
Please clarify further with some examples.

Re: Possible loop in DAX

turcelaygoy — Wed, 21 Jun 2023 10:36:22 GMT

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.

Re: Possible loop in DAX

tamerj1 — Wed, 21 Jun 2023 11:07:11 GMT

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

Re: Possible loop in DAX

turcelaygoy — Wed, 21 Jun 2023 11:47:32 GMT

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

Re: Possible loop in DAX

ppm1 — Wed, 21 Jun 2023 11:53:16 GMT

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

Re: Possible loop in DAX

tamerj1 — Wed, 21 Jun 2023 11:55:32 GMT

turcelaygoy
I believe I need to see some screenshots.

Re: Possible loop in DAX

turcelaygoy — Mon, 26 Jun 2023 07:14:33 GMT

nlka

Re: Possible loop in DAX

tamerj1 — Thu, 22 Jun 2023 07:01:51 GMT

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.

Re: Possible loop in DAX

turcelaygoy — Thu, 22 Jun 2023 07:08:44 GMT

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.

Re: Possible loop in DAX

devanshi — Thu, 22 Jun 2023 07:25:23 GMT

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

Re: Possible loop in DAX

tamerj1 — Thu, 22 Jun 2023 07:40:22 GMT

turcelaygoy

Can you share a sample file?

Re: Possible loop in DAX

turcelaygoy — Thu, 22 Jun 2023 07:42:03 GMT

of the excel sheet or the power BI

Re: Possible loop in DAX

tamerj1 — Thu, 22 Jun 2023 07:44:19 GMT

Power Bi

Re: Possible loop in DAX

turcelaygoy — Thu, 22 Jun 2023 07:48:20 GMT

It doesn't let me share the file

Re: Possible loop in DAX

tamerj1 — Thu, 22 Jun 2023 07:54:19 GMT

turcelaygoy
You can only share a download link.

Re: Possible loop in DAX

turcelaygoy — Mon, 26 Jun 2023 07:13:45 GMT

Link

Re: Possible loop in DAX

turcelaygoy — Fri, 23 Jun 2023 06:36:35 GMT

Does this link work? tamerj1

Re: Possible loop in DAX

tamerj1 — Fri, 23 Jun 2023 06:47:22 GMT

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.

Re: Possible loop in DAX

turcelaygoy — Fri, 23 Jun 2023 08:06:19 GMT

No problem!!