*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 * x _{1} + Slope2 * x_{2} + … + 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.

## Be the first to comment