Implementing linear regression in Power BI
HTML-код
- Опубликовано: 1 авг 2024
- How to manage the LINESTX function results in DAX to compute linear regression.
Article and download: sql.bi/800442?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... 
Наука
Great video, Marco! Thank you for sharing this insightful tutorial. Typically, I perform regression analysis in Python and get the coefficients and intercept into Power BI for predicting values. However, with the LINESTX function in Power BI, I can now conveniently run the regression directly within Power BI and obtain the linear equation. This approach is highly efficient, dynamic and saves me time. Thanks again for introducing this helpful technique!
Great explanation. My brain is back to statistics
I assumed linest stood for linear estimator- hence the function would be pronounced lin-est. Not a problem though!
Thanks for all the great content. You guys are life savers
Thank you for the video, Marco! It works! The question is - how to look for a linear regression for the future months where we still have no sales?
Awesome sir 😊
Thank you so much!
Hi,
So, for single row table the SELECTCOLUMNS() returns a scalar value, similar to VALUES().. Thank you!
Like, regards, from Cali Colombia. I suggest you use Excel's linear forecast function to explain this topic.
Hi! How many times are we calculating the linear regression? Seems to be expensive. Thanks for this video!
Thank youuuuuuuuuu*
What about polynomial regression ? Is it possible?
Quick question, changing subject...any new about the issue in Bravo not running after PBI May update? Thanks.
We just released an update - please check it and report if you have any issue on the dedicated area github.com/sql-bi/Bravo/issues
Once you have created the linear regression measure, how do you actually add the line to your chart?
You add the measure to the chart! Look at the sample file, you can download it from the article linked in the description.
Let's say I have a table with 100 products with all the Prices and Quantities. I'd like to do linear regression with many different products.
How do I use LINESTX in a smart way, avoiding to write 100 DAX queries?
How can I link a dropdown list of the products, located in one tab of our dashboard, to LINESTX function? Is that possible?
The LINEST executed in a measure is automatically applied to each product if you chart them. Technically there are hundreds of executions of the function but in different filter contexts.
Yes I'm facing the same bug issue. How to solve this
Which bug?
@@SQLBI where we cannot pick slope1 using selectcolumn
Ignore the red line under the function, it's a bug of the editor in Power BI Desktop. The code works, a real error would display a message on a yellow background - that is a real error.
Does LINESTX still not work for polynomial regression?
LINE stands for linear... we should wait for a dedicated function.
@@SQLBI LINEST in Excel works, for e.g. a 2nd order polynomial...
Find slope and intercept coefficients with...
LINEST(KnownY,KnownX^{1,2},,TRUE)
Then plot line with...
(Slope1*(KnownX^2))+(Slope2*KnownX)+Intercept
Or 3rd order polynomial...
LINEST(KnownY,KnownX^{1,2,3},,TRUE)
(Slope1*(KnownX^3))+(Slope2*(KnownX^2))+(Slope3*KnownX)+Intercept
Higher orders follow the same pattern
Sorry, look at the LINESTX syntax which is more flexible:
dax.guide/linestx/
It blown my mind! 🥲