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!
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 this useful tutorial. Quick question: what would be the syntax for LINESTX if I want to filter the initial table? For example, in my table I have multiple product categories and I want calculate only for one of them. Would the formula become as below (assuming I am interested in Bikes)? Table 3 = LINESTX (FILTER('Table','Table'[Category]="Bikes"),'Table'[Revenue],'Table'[Sold date],TRUE)
Thanks so much! That MS is still not (2025) implementing an automatic display of the formula is really annoying... MS Excel had this for years or decades even
Surprisingly not expensive at all. I thought the same, let it calculate for a maximum of 83.246 days on the x-axis and couldn't noticed a lag when slicing the years.
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.
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.
@@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
![Blox Fruits Dragon Rework Update [Full Stream]](http://i.ytimg.com/vi/EqDAp8udhm0/mqdefault.jpg)
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!
Very useful. Saved my day
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
Hi,
So, for single row table the SELECTCOLUMNS() returns a scalar value, similar to VALUES().. Thank you!
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?
Thank you for this useful tutorial. Quick question: what would be the syntax for LINESTX if I want to filter the initial table? For example, in my table I have multiple product categories and I want calculate only for one of them. Would the formula become as below (assuming I am interested in Bikes)?
Table 3 = LINESTX
(FILTER('Table','Table'[Category]="Bikes"),'Table'[Revenue],'Table'[Sold date],TRUE)
How about if I want to calculate for multiple products on the same plots?
Thanks so much! That MS is still not (2025) implementing an automatic display of the formula is really annoying... MS Excel had this for years or decades even
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.
What about polynomial regression ? Is it possible?
Great explanation. My brain is back to statistics
Hi! How many times are we calculating the linear regression? Seems to be expensive. Thanks for this video!
Surprisingly not expensive at all. I thought the same, let it calculate for a maximum of 83.246 days on the x-axis and couldn't noticed a lag when slicing the years.
Like, regards, from Cali Colombia. I suggest you use Excel's linear forecast function to explain this topic.
Awesome sir 😊
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/
Thank you so much!
Thank youuuuuuuuuu*
Quite complicated, it is much easier to take the data back to excel or python and make the regression line
It blown my mind! 🥲
terrible explanation