Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Recursion in DAX

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!

  • daXtreme's avatar
    daXtreme
    4 years ago

    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...

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      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)) 

       

      • daXtreme's avatar
        daXtreme
        Solution Sage

        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...

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable

      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!

      • Jos_Woolley's avatar
        Jos_Woolley
        Solution Sage

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

        Regards