DAX for populating column value based on previous row of same column

Question

I am trying to replicate this Excel formula or for loop that calculates the current row value based on the previous row value of the same column.
When value = 1, Result = Rank * 1 Else, Result = Rank * previous Result

| Value | Rank | Result |
| ----- | ---- | ------ |
|   1   |   3  |   3    |
|   2   |   2  |   6    |
|   3   |   1  |   6    |

I already tried generating a series table to get the Value and Rank columns but I am unable to refer to the existing Result column to update the same. Is there a way to get this done using DAX for dynamic number of rows?

DAX can't handle recursive calculations... The only realistic option here is to pre- calculate this field at the source and then import it. — RADO
– RADO, Commented Feb 11, 2021 at 9:03
@RADO, any suggestions for replicating this on SQL server then? like a stored proc? — Sam25
– Sam25, Commented Feb 11, 2021 at 12:11
Yes, you can use a stored procedure and write the recursion in SQL. I'd recommend to post it as a separate question with the appropriate tags. — RADO
– RADO, Commented Feb 11, 2021 at 12:26
@RADO that's true - it cannot handle recursion, but it's powerful enough to solve this problem - look at my answer. — W.B.
– W.B., Commented Feb 11, 2021 at 12:44

W.B. · Accepted Answer · 2021-02-11 12:48:58Z

1

It can be done, but you need to get your mindset out of recursion. Notice that I did [Rank] even for [Value] equal 1, as [Rank] * [Value] in that case is equal [Rank] (value equals 1, right?).

Result =
IF (
    Table[Value] = 1,
    [Rank],
    [Rank] *
    PRODUCTX (
        FILTER ( Table, Table[Value] < EARLIER ( Table[Value] ) ),
        [Rank]
    )
)

EDIT:

The previous one was unneccessarily complex:

Result =
CALCULATE (
    PRODUCT ( [Rank] ),
    FILTER ( Table, [Value] <= EARLIER ( [Value] ) )
)

edited Feb 11, 2021 at 12:48

answered Feb 11, 2021 at 12:43

W.B.

5,54321 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam25 Over a year ago

that does help actually! I was playing around with SUMX,MINX along with EARLIER but completely missed PRODUCTX which does the trick - so thank you!

Collectives™ on Stack Overflow

DAX for populating column value based on previous row of same column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related