Forum Discussion
Dax function for conditional formatting cells in matrix
Hi everyone,
I would like to know how can I go about creating a DAX function that can conditionally format the colour of the cells in the matrix table.
▶️I would like to conditonal format specifically column "Actual" and "YTD".
The rules that I would like to implement is:
1. If the value is less than "Threshold", cell background to be red
2. If the value is between "Threshold" and "Target", cell background to be yellow
3. If the value is between "Target" and "Stretch", cell background to be blue
4. If the value is greater than "Stretch", cell background to be green
▶️Things to note is that, the "Threshold", "Target", "Stretch", "Actual", "YTD" values will be different for different years and months (i.e., users will get to pick the specific year and month he/she is interested in filter as shown in the slicer created).
Thanks lot for your help 😁
Hi Anonymous,
>>How do I modify the DAX function such that if the cell is blank (i.e., does not have any value), the cell background will be grey?
You need to confirm these blank cells are really include in your table instead of expanded by category fields, the conditional formatting feature should not works on not existed records.
For this scenario, you need to use unconnected tables with current category types as row and column axis, then you can write measure formula to use current row context to lookup raw table records. After these steps, you can do conditions formatting on this visual blank parts.
Regards,
Xiaoxin Sheng
3 Replies
Some update: I have managed to write the DAX function:
PSC Actual = switch(true(), sum('KPI'[Actual]) > sum('KPI'[Stretch]) , "#34A853", sum('KPI'[Actual]) < sum('KPI'[Threshold]), "#EA4335", sum('KPI'[Target]) <= sum('KPI'[Target]) ,"#FBBC05","#8EB6F8")How do I modify the DAX function such that if the cell is blank (i.e., does not have any value), the cell background will be grey?Hi Anonymous,
>>How do I modify the DAX function such that if the cell is blank (i.e., does not have any value), the cell background will be grey?
You need to confirm these blank cells are really include in your table instead of expanded by category fields, the conditional formatting feature should not works on not existed records.
For this scenario, you need to use unconnected tables with current category types as row and column axis, then you can write measure formula to use current row context to lookup raw table records. After these steps, you can do conditions formatting on this visual blank parts.
Regards,
Xiaoxin Sheng
I forgot to add that, there are two instances where it uses an inverse scale (i.e., Non- Energy Cash Cost and LOC Incidents), in that case the rules will be the opposite of the ones mentioned above, where it has to be less than "Stretch" to be green and greater than "Threshold" to be red. Not sure how do I go about modifying the DAX function.....