topic Re: Recursion in DAX in DAX Commands and Tips

Recursion in DAX

Anonymous — Tue, 12 Jul 2022 10:13:31 GMT

Hi team,

I have a DAX measure (not calculated column) that calculates monthly KPI, and needs to refer to it's previous months' result to get current month number.

It's like something below:

I already have a DAX measure to calculate X = x1, x2, x3, ... (for each month)

Current month X = CALCULATE(SUMX(MyTable, [X]))

Cumulative X = var ThisMonth = SELECTEDVALUE(MyCalendar[Month]) return CALCULATE(SUMX(MyTable, [Current month X]), All(MyCalendar), MyCalendar[Month] <= ThisMonth))

However, for Y, it's calculated based on

[Current month Y] = [Current month's cumulative X] - [Previous month's cumulative Y]

where [Previous month's cumulative Y] is the sum of [Current month Y] for every month until previous month

This is causing a circular dependency in my DAX measure:

Current month Y = CALCULATE(SUMX(MyTable, [Cumulative X] - [Cumulative Y (last month)]))

Cumulative Y (last month) = var ThisMonth = SELECTEDVALUE(MyCalendar[Month]) var LastMonth = EDATE(ThisMonth, -1) return CALCULATE(SUMX(MyTable, [Current month Y]), All(MyCalendar), MyCalendar[Month] <= LastMonth))

I saw some post saying that DAX cannot do recursively self-reference, and I also cannot write table to store the previous month's calculation result as I need the calculation to be on-the-fly.

Any tips or workaround idea would be very much appreciated! Thank you!

Re: Recursion in DAX

amitchandak — Tue, 12 Jul 2022 10:56:43 GMT

Anonymous , based on screen shot, seem like you need

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Re: Recursion in DAX

Anonymous — Tue, 12 Jul 2022 11:16:34 GMT

hi amitchandak ,

thanks for the quick response! I did tried something like that, however, as the current month's [Y] is based on it's previous month's cumulative [Y] result, it will cause a circular dependency error if I tried to calculate cumulative [Y]

Current month Y = CALCULATE(SUMX(MyTable, [Cumulative X] - [Cumulative Y (last month)]))

Cumulative Y (last month) = 
var ThisMonth = SELECTEDVALUE(MyCalendar[Month])
var LastMonth = EDATE(ThisMonth, -1)
return CALCULATE(SUMX(MyTable, [Current month Y]), All(MyCalendar), MyCalendar[Month] <= LastMonth))

Re: Recursion in DAX

v-chenwuz-msft — Fri, 15 Jul 2022 09:08:42 GMT

Hi Anonymous ,

Indeed, it is not possible to compute formulas with circular dependencies in power bi.

However, we can avoid this problem from a mathematical point of view. It seems to follow from the screenshot that x=y, and you can try to prove it.

If i misunderstood you, please share some example data and expcet result.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Recursion in DAX

Anonymous — Fri, 15 Jul 2022 10:11:39 GMT

Hi v-chenwuz-msft

you were right about the example here (sorry I was trying to simplify the problem to see if I can find a general solution for a recursive function)

The actual problem I was trying to solve is a bit more complicated than the example, where

y1 = IF(x1 >0, x1, 0)

y2 = IF( (x1+x2)-(y1) >0, (x1+x2)- (y1), 0)

and so on

so x is only equal to y when the difference between cumulative x and last cumulative y is all positive

Any suggestions for this? Thank you!

Re: Recursion in DAX

Jos_Woolley — Fri, 15 Jul 2022 12:47:53 GMT

Perhaps post some more realistic data (copiable, not a picture), together with expected results.

Regards

Re: Recursion in DAX

daXtreme — Sun, 17 Jul 2022 08:56:33 GMT

Hi Anonymous

If you have a problem that calls for recursion in DAX... well, then you've basically got 2 choices: 1) either you'll find a non-recursive formula or 2) you'll move the calculation to Power Query/source system. The best solution is to be able to find a non-recursive formula, of course, and sometimes it's possible with a bit of mathematical wizardry but sometimes, sadly, it's simply impossible or just too difficult to do. Then, of course, you don't have a choice 😞 By experience I can tell you that if you have a formula with IF's in it and it's recursive, then there are very slim chances to obtain an iterative one...

Re: Recursion in DAX

v-chenwuz-msft — Mon, 18 Jul 2022 07:42:33 GMT

Hi Anonymous ,

To my knowledge, this doesn't seem very possible. You could try doing the calculations in excel first and then import it into power bi.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.