cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Measure that refers to its own previous value

Hi everyone, I'm trying to solve this problem,

Lets say I want to get the folowing siquence = "previos value" * 3 +2 .

So for the second row 2*3+2=8, third 8*3+2=26 and so on.

Value = IF( MAX("Table[Index]")=1, 2, ...

 Index Value 1 2 2 8 3 26 4 80 5 242 6 728
1 ACCEPTED SOLUTION
Helper I

I found it, it is actually arithmetico geometric series. And it goes like this

It took me some time to figure it out, but it works. Thanks again for the idea.

4 REPLIES 4
Super User

Hi @xxenoss

It isn't possible for a DAX measure (or calculated column) to refer to itself, so traditional recursion isn't possible in DAX.

In these sorts of situations, I recommend doing a little algebra to convert the calculation to something that can be summed using SUMX over a cumulative set of index values, or some other closed form expression.

In your example, it turns out that your values are the sum of terms of a geometric sequence multiplied by 2.

i.e. 2*(1), 2*(1+3), 2*(1+3+3^2), 2*(1+3+3^2+3^3),...

So after applying the formula for the sum of a geometric sequence and simplifying, you get

Value(Index) = 3^(Index) - 1

In DAX you can write:

``````Value =
VAR MaxIndex = MAX ( 'Table'[Index] )
RETURN
POWER ( 3, MaxIndex ) - 1

-- Sum of first n terms of geometric sequence =
--    a1 * (r^n - 1 ) / ( r - 1 )
-- We have a1 = 1 & r = 3
-- multiplied by 2, giving
-- 2 * (POWER ( 3, MaxIndex ) - 1) / (3 - 1 )
-- Simplifies to expression above``````

You could get the same result using an iterator to sum the terms, but performance would be worse:

``````Value =
VAR MaxIndex = MAX ( 'Table'[Index] )
VAR IndexValuesModified =
GENERATESERIES ( 0, MaxIndex - 1 )
RETURN
2 *
SUMX (
IndexValuesModified,
POWER ( 3, [Value] )
)``````

Regards,

Owen

Owen Auger
Blog
Helper I

I found it, it is actually arithmetico geometric series. And it goes like this

It took me some time to figure it out, but it works. Thanks again for the idea.

Community Support

Hi, @xxenoss

For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

Helper I

Thank you so much! This worked great for this particular sequence! But is there a way to make this formula universal? Like Value = x*y+x, "Previous Value" * y+x, "Previous Value" * y+x, ...

You probably explained it here, but my math knowledgeis not sufficient to understand.

-- Sum of first n terms of geometric sequence =
-- a1 * (r^n - 1 ) / ( r - 1 )
-- We have a1 = 1 & r = 3
-- multiplied by 2, giving
-- 2 * (POWER ( 3, MaxIndex ) - 1) / (3 - 1 )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.