This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Changes in the February 2023 Power BI Update Part seven of an eight-part series of blogs
There are quite a few changes in this month's update, but none of them will change your life that much! Changes in the February 2023 Power BI Update The new relationship editor Conditional formatting based on string values Choosing pages to show in the Page Navigator visual Indenting text in textboxes New accessibility themes Two new DAX functions: LINEST and LINESTX (this blog) You can now change image height and width separately We've been creating our idiosyncratic monthly blogs on Power BI updates since November 2016, and also deliver online and classroom Power BI courses.

Changes in the February 2023 Power BI Update

Part seven of an eight-part series of blogs

There are quite a few changes in this month's update, but none of them will change your life that much!

Changes in the February 2023 Power BI Update
The new relationship editor
Conditional formatting based on string values
Choosing pages to show in the Page Navigator visual
Indenting text in textboxes
New accessibility themes
Two new DAX functions: LINEST and LINESTX (this blog)
You can now change image height and width separately

We've been creating our idiosyncratic monthly blogs on Power BI updates since November 2016, and also deliver online and classroom Power BI courses.

Posted by Andy Brown on 28 February 2023

In this blog

Linear Trendlines
The LINEST function
Problems with these new functions

Two new DAX functions: LINEST and LINESTX

For all you fans of least-squares linear regression out there - two new DAX functions!

Linear Trendlines

Power BI already gives you the ability to show trendlines:

A trendline showing how sales broadly speaking go up in line with shop unit sizes for the different towns in the Wise Owl Create-a-Creature database.

As a reminder, here's how you add a trendline to a chart like this:

You can use the analytics tab to add trendlines like this to charts.

The LINEST function

Suppose that you have a table giving the sales and number of units for each town:

The sales and number of shop units for each town in the Create a Creature database.

You can now create a measure to give the intercept of a regression line based on this data:

RegressionIntercept =

// get two columns of data for analysis

VAR RegressionStats =

LINEST(

Town[Sales],

Town[Units]

)

// the LINEST function returns a single row table with

// a number of columms - the main ones we will normally

// be interested in are the intercept and the slope

RETURN MAXX(

RegressionStats,

[Intercept]

)

You can create an almost-identical measure to get the slope of the regression line:

RegressionIntercept =

// get two columns of data for analysis

VAR RegressionStats =

LINEST(

Town[Sales],

Town[Units]

)

// the LINEST function returns a single row table with

// a number of columms - the main ones we will normally

// be interested in are the intercept and the slope

RETURN MAXX(

RegressionStats,

[Slope1]

)

Below I've then displayed the results from these two measures on cards:

So the equation of the regression line for this data should be y = 25.37 x - 42.79.

The LINESTX function works in a similar way, but like any other expression function asks for a table as its first argument.

Problems with these new functions

The main problem with these new functions is knowing what the created columns are called:

The Intercept and Slope1 names don't appear in Intellisense, and the red wavy underline suggests that they're wrong!

You can see the available columns that you can reference for the LINEST function here and for LINESTX here.

Parts of this blog
Changes in the February 2023 Power BI Update The new relationship editor Conditional formatting based on string values Choosing pages to show in the Page Navigator visual Indenting text in textboxes New accessibility themes Two new DAX functions: LINEST and LINESTX (this blog) You can now change image height and width separately

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Two new DAX functions: LINEST and LINESTX

Linear Trendlines

The LINEST function

Problems with these new functions

Head office

London

Manchester