Forum Discussion
Problems with conditional formatting in matrix table with row sublevels.
Hello!
I am making a dashboard that shows the planned hours per person per week.
On top of that, If someone's planned hours is +4 or -4 compared to their working hours, the value should turn red or green to indicate. This works perfectly for the main level of the matrix (Each person has a row). See example below.
However, with the + icon it's possible to drill down to project level. In this example for a specific person it will show all their projects.
And now the colours don't do what they are supposed to do.
This example person has planned 44 hours per week for the forseeable future, but his capacity is only 40. For that reason it shows red in the main view which is correct. However when I open the selection to show all projects, my formula will compare each row seperately. And give all values a green because they are all more than 4 below 40. But this is wrong. These values should not get a colour.
My question is: How do I disable the conditional formatting for this sub-level?
In the conditional formatting I can select to apply it to values, totals, or both. Sadly this doesnt work, because both situations are considered values.
Change the conditional formatting source from rules to a field value, and create a measure that provides the desired color. Use ISINSCOPE() to figure out where in the hierarchy you are.
4 Replies
Change the conditional formatting source from rules to a field value, and create a measure that provides the desired color. Use ISINSCOPE() to figure out where in the hierarchy you are.
This is very useful, thank you!
Can you clarify a little bit more on how to create this measure? I've tried creating a simple measure to test if the formatting works, but I am not able to select it at the field formatting selection pane. I can't click it. My measure is probably wrong.
Can you tell me where I'm going wrong?
Formatting = IF(SUM(Gepland[Hours])> 40, "#b20000", "#a30000")
That should work. Can you maybe post a sample .pbix ?
HI Anonymous,
You can conditional formatting 'field value' mode with measure formula and add if statement int to the expression to check current row and column values. Then you can use these values as conditions for color formatting.
Reference links:
Conditional table formatting in Power BI Desktop - Power BI | Microsoft Docs
Clever Hierarchy Handling in DAX - SQLBI
Notice: this measure expression should return color names or hex color strings.
Regards,
Xiaoxin Sheng