The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 😁
Solved! Go to Solution.
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.....
Some update: I have managed to write the DAX function:
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
32 | |
20 | |
15 | |
15 |
User | Count |
---|---|
94 | |
33 | |
30 | |
24 | |
21 |