cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
noah-severyn
Advocate I
Advocate I

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.

 

noahseveryn_2-1628885831487.png

 

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

1 ACCEPTED SOLUTION
AlexisOlson
Super User
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] ) )

View solution in original post

7 REPLIES 7
kopite
New Member

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

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 

AlexisOlson
Super User
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?

This is what I get when I make that change:

AlexisOlson_1-1629124708618.png

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.

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors