Although the conditional formatting by rules feature in Power BI was released a long time ago, one very common cause of confusion is with how to implement basic “greater than” or “less than” rules. For example, say you have a table with the following data in it:
…and you want to highlight the rows where Sales are greater than or equal to 150. So you click on the table, go to the Conditional Formatting options for the Sales field, turn on Background Color formatting and click Advanced Controls:
…then choose to format by rules. You see this dialog:
In particular, the part of this dialog where you set up the rule:
…seems to suggest that you need to enter a “is greater than or equal to” condition and a “is less than” condition for the rule to be valid. This is not true, and you don’t need to enter some arbitrarily large number for the “is less than” condition to make it work. The following screenshot shows how you can set up a rule to highlight all rows where Sales are greater than or equal to 150:
The two things to notice are:
- I’ve entered 150 in the first condition, as you would expect
- I have deleted the 0 from the second “is less than” condition, leaving the textbox empty (meaning that the text “Maximum” is visible but greyed out)
Here’s the result:
Job done. What about a slightly more complex but nonetheless common scenario where values greater than 0 are shown as green, values equal to 0 as yellow and values less than 0 as red (with maybe some special handling for blanks too)? Here’s a sample table:
(The Dummy column is only there to make sure the Oranges row, which contains a blank value, is visible in the table)
Here’s a set of rules that does what we need:
And here’s the output:
I was confused. Thanks for clearing this up
I was confused too. I think everyone was.
99999999, we hardly knew you!
Thanks Chris, appreciate the post. I still think it’s a really poor user experience and should be made much clearer to set simple rules. So many users will give up on this…
Coming from Tableau, fancy colored visualization is not Power BI’s strength. But It was a good tip.
THANK YOU!!!!!!!!!!!!!!
Shannon Rubsamen
Database Administrator/Report Writer
United Jewish Federation of Tidewater/Simon Family JCC
The Reba & Sam Sandler Family Campus of the Tidewater Jewish Community
5000 Corporate Woods Drive, Suite 200
Virginia Beach, VA 23462-4370
(757) 965-6100 | (757) 965-6131 (Direct ext) | (757) 961-3301 (fax)
Is it possible to apply formatting to “text” values…as in your example…highlight “Oranges” as a Product value.
Great article Chris! You beat me to this topic actually! I have a video planned for this topic as well. But now I’ll probably just link to your article 🙂
I know there must be a better way than 999999999!!! Thanks for this, very helpful
Hi Chris, does this not work anymore? Although I can erase the value I want ignored, and I see the box then say Minimum or Maximum, when I return to the dialog the fixed numbers are back. Thanks!
It still seems to work for me – when I return to the dialog there are fixed numbers, but they are percentages not actual values and it all works as expected.
Thanks a lot for this!!