In this writing, I want to share how I started playing with the new DAX function, released a few days ago, LINESTX in Power BI.
The definition that I understand is, the LINESTX DAX function provides statistical information in order to create a single line that best matches the given data, then returns a one row table that describes the line.
In other words, the function creates a single-row table, and this new table contains some of the statistical information that is generated by using the data that was input in the formula.
I will try to demonstrate it by using simple data in some playgrounds.
Playground 1.
I have a below table.
Table name: Data
Description: It shows Salary amount by WorkYear.
By using the LINESTX DAX function, and inserting information of the above into the function, it creates a new table like below.
EVALUATE
LINESTX( Data, Data[Salary], Data[WorkYear])
Please understand more about the syntax and parameters in the function from the link down below.
How I play with it: The newly created table can be used inside a measure as a virtual table.
Here, I will only use two columns from the newly created table, that are [Slope1] and [Intercept], in order to use those in the equation like below.
Salary amount = Intercept + Slope1 X WorkYear
For now, I only have WorkYear information from 5 to 12, as shown in the above.
If I use the equation, I can create a new table that describes an estimated salary amount for WorkYear between 1 and 20.
Estimate Salary Table = VAR _linestx = LINESTX ( Data, Data[Salary], Data[WorkYear] ) VAR _newtable = SELECTCOLUMNS ( GENERATESERIES ( 1, 20, 1 ), "@Year", [Value] ) VAR _addestimatesalary = ADDCOLUMNS ( _newtable, "@estimatesalary", MAXX ( _linestx, [Intercept] ) + ( MAXX ( _linestx, [Slope1] ) * [@Year] ) ) RETURN _addestimatesalary
Let's play with a table that contains [WorkYear] column and [Skill level] column. In other words, [Salary] column in data depends on [WorkYear] and [Skill level].
Playground 2.
I have a below table.
Table name: Data with skills
Description: It shows Salary amount by WorkYear and by skill level.
EVALUATE
LINESTX(
'Data with skills',
'Data with skills'[Salary],
'Data with skills'[WorkYear],
'Data with skills'[Skill level]
)
Here, I will use three columns from the newly created table, that are [Slope1], [Slope2], and [Intercept], in order to use those in the equation like below.
Salary amount = Intercept + Slope1 X WorkYear + Slope2 X Skill level
For now, I only have WorkYear information from 5 to 12.
If I can use the above equation, I can create a new table that describes an estimated salary amount for WorkYear between 1 and 20 with the different skill level. In this sample, I created a skill level randomly per WorkYear.
Estimate Salary with Skills Table = VAR _linestx = LINESTX ( 'Data with skills', 'Data with skills'[Salary], 'Data with skills'[WorkYear], 'Data with skills'[Skill level] ) VAR _newtable = ADDCOLUMNS ( SELECTCOLUMNS ( GENERATESERIES ( 1, 20, 1 ), "@Year", [Value] ), "@Skill level", RANDBETWEEN ( 1, 4 ) ) VAR _addestimatesalary = ADDCOLUMNS ( _newtable, "@estimatesalary", MAXX ( _linestx, [Intercept] ) + ( MAXX ( _linestx, [Slope1] ) * [@Year] ) + ( MAXX ( _linestx, [Slope2] ) * [@Skill level] ) ) RETURN _addestimatesalary
To summarize, the LINESTX DAX function creates statistical information in order to create a single-straight line that best matches the given data, then returns a one-row table that contains some data that describes the line. The information can be used inside an equation in order to create an estimated information that best matches existing data.
For now, I only played with only some of the columns in the newly created table. The next step for me is to check other columns in the table that is created by the LINESTX DAX function and find out where can I use those.
I attached the sample pbix file below.
I hope this blog post helps to start utilizing the LINESTX DAX function to have more fun playing with Power BI.
Comments