Power BI Blog: LINEST and LINESTX Now Added to Power BI
13 April 2023
Welcome back to this week’s edition of the Power BI blog series. This week, we look at two new DAX functions just added to the Power BI DAX library.
Two new statistical DAX functions have just been added to the Power BI repertoire: LINEST and LINESTX. These two functions perform linear regression, leveraging the Least Squares method, to calculate a straight line that best fits the given data and return a table describing that line. These functions are especially useful in predicting unknown dependent values (y) given known independent values (x).
Both functions return a single-row table describing the line and additional statistics. The resulting table includes columns such as slopes, intercepts, standard errors and the coefficient of determination. The equation of the fitted line can be constructed as follows
y = Slope1 * x1 + Slope2 * x2 + … + Intercept.
The difference between LINEST and LINESTX is that LINEST expects columns of known x and y values to be provided, whereas LINESTX expects a table and expressions to be evaluated for each row of the table to obtain the x and y values.
For the following examples, consider the following data, which includes Sales Amount and Gross National Product, GNP_Per_Capita:
In the example below, we will use LINESTX to predict total sales based upon GNP per capita:
LinestX_example =
VAR CountryGNP = SUMMARIZE(
Sales,
'GNP_Country'[Country],
'GNP_Country'[GNP_Per_Capita],
"Total Sales", SUM(Sales[Sales Amount])
)
VAR SalesPrediction = LINESTX(
'CountryGNP',
[Total Sales],
[GNP_Per_Capita]
)
VAR Example_GNP_Per_Capita = 50000
RETURN SELECTCOLUMNS(
SalesPrediction,
[Slope1]
) * Example_GNP_Per_Capita +
SELECTCOLUMNS(
SalesPrediction,
[Intercept]
)
This expression not only leverages LINESTX but also leverages the result to perform a prediction for a fictitious country with gross national product per capita of $50,000. The result is a predicted total sales of $17,426,123.29. Of course, this is a fabricated scenario and it’s rare to have a fixed value such as the $50,000 above as part of the expression.
We may do the same using LINEST assuming the required tables are all in the model, e.g. as calculated tables. In this example, we’ve added the following calculated tables:
- CountryDetails, defined as:
CountryDetails = SUMMARIZECOLUMNS(
'GNP_Country'[Country],
'GNP_Country'[GNP_Per_Capita],
"Total
Sales", SUM(Sales[Sales Amount]))
- SalesPredictionLINEST, defined as:
SalesPredictionLINEST = LINEST('CountryDetails'[Total Sales], 'CountryDetails'[GNP_Per_Capita]).
Now we may use following measure expression to obtain the same result as above:
Linest_example =
VAR Example_GNP_Per_Capita = 50000
RETURN
MAX ( SalesPredictionLINEST[Slope1] ) * Example_GNP_Per_Capita
+ MAX ( SalesPredictionLINEST[Intercept] )
In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.
