Highlight and total based on value

[azakir]

Hi Guys.  

Looking for a way to highlight the job type (text) when the same employee changes the job title between the dates specified, and finally calculating the total number. For example: 

Based on the information above, out of 4 employees, 3 of them changed title between the dates specified. 

[pi_eye · Accepted solution]

Hi Azakir

This is a great opportunity to use the new OffSet function in PowerBI! Previously, this would have been quite hard to achieve. As it is not officially release, there is no documentation or intellisense, but I have been using it for sometime so worked out a way to do it.

First, you need to create one field as a sort order. EG 

EmpAndDateSort = Sheet1[Employee] & Sheet1[Date]

And then a measure for the role - as I can only get the function to work this way. This expression is a hack to return the current role as text:

RoleMeasure = FIRSTNONBLANK(Sheet1[Role],0)

The offset function can then give you an expression which returns the "previous rows value" as if it was ordered like a table

OffsetCalc = Calculate([RoleMeasure], OFFSET(-1,ALLEXCEPT(Sheet1,Sheet1[Employee]  ), orderby(Sheet1[EmpAndDateSort],asc )))

"All except" in this instance tells DAX to make a line at the employee dimension - otherwise the offset value would be continuous between employees (which we dont want)

Visualised, this looks like (with my dummy data)

You can see now it is easy to make a comparison on the "current" rows role, and the offset role. this can then be used to return a value that can be used in conditional formatting for the cell.

This measure looks like  

Comparison Calc = if( and([OffsetCalc]<>[RoleMeasure],not isblank([OffsetCalc])),1,0).

Note I had to exclude non blank values as these are represent the "first" row of each employee's data

Once you have these elements, the cell backgrounds in a pivot table can be edited under the chart heading -> cell elements -> background colour

Click "fx" and use this comparison measure to set the colour

This is the result:

Hope this helps

Pi

[azakir]

Hi @pi_eye 

thank you so much. This is such a great solution alongwith the explanation. I really appreciate the time you took to not only provide me with a solution, but also explain it in such a manner, that it's all clear in my mind. This solution worked wonders for me so thank you so much for that. 

[pi_eye]

You're welcome. It is a new function and I was going to do  some research on it anyway - so it is learning for me too!

[pi_eye]

Forgot to mention - you can sum the "comparison calc" above to get the total number of changes, as the values are 1 and 0!