Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Iterative loop in DAX

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

2 Replies

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    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]

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.