topic Iterative loop in DAX in DAX Commands and Tips

Iterative loop in DAX

Anonymous — Thu, 01 Aug 2024 14:38:19 GMT

Hello Everyone,

I'm trying to find a way to iterative loop through a previous calculation that will be created by a calculated column in DAX. I have to replace an existing .vb application with a PBI report.

This is the .vb code to be replaced:
If f.TCP <> 0D Then ATG = f.MAA - PAC
If ATG > CAAP Then TACC = ATG - CAAP
Else TACC = 0D
End If PAC += TACC

Else TACC = 0D

This is what i have in DAX so far:

TACC =

VAR ATG = IF([f.TCP] <> 0, [f.MAA] - [PAC],0)

VAR TACC = IF(ATG > [CAAP],ATG - [CAAP],0)

RETURN TACC

My issue seems to be with PAC creating a circular dependency. Many forums online have resources for creating in M however I would like to do it in DAX, if possible. TIA

Re: Iterative loop in DAX

aduguid — Thu, 01 Aug 2024 14:58:20 GMT

Try this measure.

PAC = VAR CurrentRowID = [RowID] -- Assuming you have a unique identifier for each row VAR PrevPAC = CALCULATE( SUM([TACC]), FILTER( ALL('YourTable'), 'YourTable'[RowID] < CurrentRowID ) ) RETURN IF(ISBLANK(PrevPAC), 0, PrevPAC) + [TACC]

Re: Iterative loop in DAX

Anonymous — Thu, 01 Aug 2024 17:19:11 GMT

~~aduguid I do not have one but I can create one. Would it be better to create RowID in M or DAX? Does it matter? Also does it need to be a measure or can it be a calculated column?~~

I tried as a calculated column and I still get circular dependency error.