Forum Discussion
Resolver IHighlight and total based on value
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.
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 tableOffsetCalc = 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 dataOnce you have these elements, the cell backgrounds in a pivot table can be edited under the chart heading -> cell elements -> background colourClick "fx" and use this comparison measure to set the colour
This is the result:
Hope this helps
Pi
4 Replies
pi_eyeResolver IV
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 tableOffsetCalc = 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 dataOnce you have these elements, the cell backgrounds in a pivot table can be edited under the chart heading -> cell elements -> background colourClick "fx" and use this comparison measure to set the colour
This is the result:
Hope this helps
Pi
- 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!
azakirHi 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!