After I posted the short blog post, LINESTX DAX in Power BI - How I start to play with it (jihwankimcscp.wixsite.com, Alexis Olson (Alexis Olson | LinkedIn) asked me on Linkedin whether I faced any unexpected situations while using it. I did not dive deep into the function to experience more, but Alexis motivated me to learn and test it in more different cases.
I created a slightly different data model compared to the previous one.
The data model looks like below.
And the salary table looks like below.
The data shows the year 2020, the year 2021, and the year 2022, and I created a matrix visualization like below. As shown below, there are some gaps in the data. But in some scenarios, the business wants to calculate numbers and fill in the gaps.
Problem:
If the request from the business is to show a relationship between all experience_years (1 ~ 10 years) and all skill levels (1 ~ 10 levels) that generate salary information by year and use the LINESTX DAX function to show every year's information including 2023, which is not yet included in the historical data.
Furthermore, the business decides,
- not to show any information gaps.
- only relevant years for calculation are 2021 and 2022, and use data from these two years.
- only relevant experience_years for calculation are 2~6, and use data between 2 and 6.
- only relevant skill_level for calculation are 2~5, and use data between 2 and 5.
then, it will be shown something like the below.
The business can decide what is relevant data to include in the calculation and what is not relevant not to include in the calculation. And then, simply select those from the slicers.
If the business decides all historical data is relevant to include in the calculation, the report page may look something like the one below.
Solution:
The slicer selections and the table visualizations are not connected, and in order to achieve this, I created disconnected tables like below, and put those into a row field and a column field in the matrix visualization.
The measure for [Salary table 2023 measure:] is written below. The measure for the year 2022 and the year 2021 are written in a similar way.
Salary table 2023 measure: = VAR _salarytable = // this is influenced by the selections of slicers on the report page. SUMMARIZE ( Salary, 'Calendar'[Year], Experience[Experience_Years], 'Skill Level'[Skill_Level], Salary[Salary] ) VAR _linestxtable = // this creates a table with [Intercept], [Slope1], [Slope], and [Slope3] columns LINESTX ( _salarytable, Salary[Salary], 'Calendar'[Year], Experience[Experience_Years], 'Skill Level'[Skill_Level] ) VAR _yeartable = SELECTCOLUMNS ( { 2020, 2021, 2022, 2023 }, "@year", [Value] ) VAR _experienceyearstable = SELECTCOLUMNS ( { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, "@experience_years", [Value] ) VAR _skillleveltable = SELECTCOLUMNS ( { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, "@skill_level", [Value] ) VAR _newsalarytable = CROSSJOIN ( _yeartable, _experienceyearstable, _skillleveltable ) // This creates a virtual table that contains all years, all experience years, and all skill levels VAR _addsalarycolumn = ADDCOLUMNS ( _newsalarytable, "@salary", MAXX ( _linestxtable, [Intercept] ) + MAXX ( _linestxtable, [Slope1] ) * [@year] + MAXX ( _linestxtable, [Slope2] ) * [@experience_years] + MAXX ( _linestxtable, [Slope3] ) * [@skill_level] ) RETURN MAXX ( FILTER ( _addsalarycolumn, [@year] = 2023 // Change the year that your want to see && [@experience_years] = MAX ( 'Experience axis'[Experience_Years] ) && [@skill_level] = MAX ( 'Skill Level axis'[Skill_Level] ) ), [@salary] )
To summarize, because the LINESTX DAX function's first argument is a table, a virtual table can be added to the formula by using the SUMMARIZE DAX function. This allows the virtual table to be influenced by slicer selections from the report page. Additionally, because the result of LINESTX is a table, this table can be used inside a calculated measure as a virtual table in order to help further calculations. Furthermore, it helps to fill the information holes that exist in the original data.
I hope this helps to have more fun playing with the LINESTX DAX function in Power BI.
The sample pbix file is attached below.
Comments