topic Re: DAX Polynomial Regression Calculation Issue in DAX Commands and Tips

DAX Polynomial Regression Calculation Issue

Anonymous — Fri, 13 Aug 2021 20:52:41 GMT

I am attempting to implement a 2nd order polynomial regression to fit some data. It appears that I can calculate the coefficients for the regression equation correctly, but I just cannot figure out how to correctly return the correct result of the equation to use in a table/chart. Starting from this solution, I ended up with the following measure:

values (poly fit) = // Reference: https://www.thedatascientists.com/polynomial-regression/ // https://metric.ma.ic.ac.uk/metric_public/matrices/inverses/inverses2.html // Because our Xs (dates) and Ys (measures) are in different tables, we have to temporarily create a new table (Known) with both of those values so we can perform SUMX operations easily. // To get the same polynomial fit as excel, the x values in the calculation are masked over with 1 thru n. Thus x-values in the calc are n+1-min(n) var countItems = COUNTX(VALUES(Table1[date]),Table1[date]) var Known = SELECTCOLUMNS ( ALLSELECTED(Table1[date]), "KnownX", [zSequence1ToN], "KnownY", CALCULATE(SUMX(Table1,[values])) ) var sumOfXs = SUMX(Known, [KnownX]) var sumOfYs = SUMX(Known, [KnownY]) var sumOfX2 = SUMX(Known, [KnownX] ^ 2) var sumOfX3 = SUMX(Known, [KnownX] ^ 3) var sumOfX4 = SUMX(Known, [KnownX] ^ 4) var sumOfXY = SUMX(Known, [KnownX] * [KnownY]) var sumOfX2Y = SUMX(Known, [KnownX] ^ 2 * [KnownY]) var determinant = countItems*((sumOfX2*sumOfX4)-(sumOfX3*sumOfX3))-sumOfXs*((sumOfXs*sumOfX4)-(sumOfX2*sumOfX3))+sumOfX2*((sumOfXs*sumOfX3)-(sumOfX2*sumOfX2)) //Calculate the inverse matrix (combining a few steps together) var M11 = ((sumOfX2*sumOfX4)-(sumOfX3*sumOfX3))/determinant var M12 = -((sumOfXs*sumOfX4)-(sumOfX3*sumOfX2))/determinant var M13 = -((sumOfX2*sumOfX2)-(sumOfXs*sumOfX3))/determinant //there was an error on this line i had to add a negative to fix. no idea why. EVERY other var evaluates correctly. var M21 = -((sumOfXs*sumOfX4)-(sumOfX2*sumOfX3))/determinant var M22 = ((countItems*sumOfX4)-(sumOfX2*sumOfX2))/determinant var M23 = -((countItems*sumOfX3)-(sumOfXs*sumOfX2))/determinant var M31 = ((sumOfXs*sumOfX3)-(sumOfX2*sumOfX2))/determinant var M32 = -((countItems*sumOfX3)-(sumOfX2*sumOfXs))/determinant var M33 = ((countItems*sumOfX2)-(sumOfXs*sumOfXs))/determinant //Assuming equation of: a*x^2 + b*x + c var c = M11*sumOfYs+M12*sumOfXY+M13*sumOfX2Y var b = M21*sumOfYs+M22*sumOfXY+M23*sumOfX2Y var a = M31*sumOfYs+M32*sumOfXY+M33*sumOfX2Y RETURN // SUMX( // DISTINCT(Table1[date]), // a * [zSequence1ToN] ^ 2 + b * [zSequence1ToN] + c // ) [a]*[zSequence1ToN]^2+[b]*[zSequence1ToN]+[c]

Where [zSequence1ToN] is a RANKX of the date values

zSequence1ToN = RANKX(ALLSELECTED(Table1[date]),CALCULATE(SUM(Table1[date])),,ASC)

My issue is I cannot figure out how to correctly calculate this for each row in the table. The screenshot below is what I am getting (compared to what I should be getting - at least according to excel.

I suspect my issue may be where I am returning the values, but at this point I am out of ideas and need advice. I have attached a sample pbix file and the excel file I was using for validation. What am I doing wrong?

https://1drv.ms/u/s!Ah3VDq5HnODQgcArelYadi0cXQQZzA?e=8YdvQK

Re: DAX Polynomial Regression Calculation Issue

AlexisOlson — Fri, 13 Aug 2021 21:39:56 GMT

I think the main problem here is this: COUNTX(VALUES(Table1[date]),Table1[date])

This is evaluated within the local filter context and returns 1 for each row corresponding to a single date.

Try this instead: COUNTROWS ( ALLSELECTED ( Table1[date] ) )

Re: DAX Polynomial Regression Calculation Issue

Anonymous — Mon, 16 Aug 2021 14:04:07 GMT

Thank you for your input, however that did not seem to have any effect. Would that have an effect on the final return result if that var only returns a single number of the count?

Re: DAX Polynomial Regression Calculation Issue

AlexisOlson — Mon, 16 Aug 2021 14:39:08 GMT

This is what I get when I make that change:

Good match except for x = 1.

Note that you need to make the same change for each of [a], [b], [c] or else change your final line of [values (poly fit)] to a*[zSequence1ToN]^2+b*[zSequence1ToN]+c (use the variables you've already computed instead of referencing other measures).

Re: DAX Polynomial Regression Calculation Issue

Anonymous — Mon, 16 Aug 2021 14:59:00 GMT

That's a facepalm moment. I was indeed using the measures instead of the variables. Thanks!

So the moral of the story is that local filter context is passed down through dependent variables too. That is something I would not have considered.

Re: DAX Polynomial Regression Calculation Issue

AlexisOlson — Mon, 16 Aug 2021 15:01:17 GMT

Yes. Each VAR is calculated within the local filter context.

Re: DAX Polynomial Regression Calculation Issue

kopite — Mon, 07 Nov 2022 01:46:58 GMT

Hi, PLEASE HELP!!

I'm prototyping a similar calculation in excel for a 4th order polynomial with a 5x5 matrix, but I cannot seem to generate the same result as LINEST() in excel, see attached....

https://1drv.ms/x/s!AsId0OAmsuzuhOEh7CR03iyUCLAbng?e=fiK5ay

I've used the Chio method of expansion to compute the determinant...

https://www.youtube.com/watch?v=_JetUVpvFAU

I've then computed matrix of minors and matrix of cofactors to work out the inverse of the original 5x5 matrix, as instructed here...

https://www.mathsisfun.com/algebra/matrix-inverse-minors-cofactors-adjugate.html

Thanks

Re: DAX Polynomial Regression Calculation Issue

kopite833 — Mon, 07 Nov 2022 05:11:10 GMT

Found a solution for a 5x5 matrix using Chio method of expansion and Cramer's Rule, parsing the solution vector into a series of matrices, to calculate determinants divided by the original determinant to solve for the polynomial regression coefficients...

https://1drv.ms/x/s!AsId0OAmsuzuhOEh7CR03iyUCLAbng?e=WKULj9

Re: DAX Polynomial Regression Calculation Issue

ToddFEagle2023 — Mon, 29 Jan 2024 23:29:04 GMT

I have a matrix with 2 columns from which i want to run a polynomial regression to the fourth degree to fit a line on data. The fourth degree is to smooth the data basically looking at daily data with a smoothing effect over a four week period. I can't seem to convert the equations of the DAX code above to give me the results of a fourth degree and struggling to convert your excel spreadsheet to those equations. Any help would be greatly appreciated.