cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

DAX Polynomial Regression Calculation Issue

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?

1 ACCEPTED SOLUTION
Super User

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

8 REPLIES 8
New Member

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

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

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

Thanks

Frequent Visitor

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

New Member

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.

Super User

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

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?

Super User

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

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.

Super User

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

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors