Root-Finding Algorithm in DAX

[WishAskedSooner]

Hi Experts!

I need to implement a fairly simple root finding algorithm in a DAX measure. E.g.

F[X(i+1)] = X(i) - F[X(i)] / F'[X(i)]

with the ability to loop until a convergence criteria is met e.g. ABS( F[X(i+1)] - F[X(i)] ) < 0.001. However, I am uncertain if DAX is capable if implementing loops.

Has anyone ever implemented such an algorithm and how did you go about doing so?

Thanks in advance!

[Deku]

You can perform loops by using generateseries() to tuple of values which you can iterate with addcolumns(). I think you want to pass the value from each iteration into the next, which I don't think is possible

[WishAskedSooner]

@Deku Thank you for your insightful reply.

If I understand correctly, this requirement isn't possible using DAX?

[Deku]

I believe so. Might be possible in powerquery

[WishAskedSooner · Accepted solution]

Hi @Deku,

I did a little research and according to Russo and Ferrari, DAX does not support full recursion. It supports something called sideways recursion, but the details of that are unclear and based on what I can tell don't apply here anyway. Apparently, MDX supports full recursion, but MS left it out of DAX for performance reasons.

I also saw Greg Deckler's post on For Loops which is helpful, but again falls short when one requires full recursion. He has an example for the Fibonacci series, but it is hack versus an actual solution.

PowerQuery is not an option either because this is a user interaction requirement.

All this brings me to my final point.

I have been using PBI over a year now, and while DAX/Vertipaq are powerful tools, in my estimation, the lack of full recursion puts PBI plainly in the category of a reporting tool only which severely handicaps its potential applications. And, I think developers need to be aware of this before considering using PBI for anything other than reporting.

I believe I can still cook up a workaround that follows Greg Deckler's Fibonacci series example, but I am not 100% certain, and it will certainly be a compromise at the end of the day.

Anyway, I am marking this reply as the solution unless someone comes in with another.

[Deku]

Sideways recursion is referencing calculation items from calaculation items in the same calculation group