Creating Basic Greater Than Or Less Than Rules With Power BI Conditional Formatting

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:


16 responses

  1. 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…

  2. 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)

  3. Is it possible to apply formatting to “text” values…as in your example…highlight “Oranges” as a Product value.

  4. 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 🙂

  5. Pingback: Last Week Reading (2019-07-07) | SQLPlayer

  6. Pingback: Creating Basic Greater Than Or Less Than Rules With Power BI Conditional Formatting |

  7. Pingback: Power BI Conditional Formatting and Icons – Curated SQL

  8. 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!

  9. Pingback: Formato condicional mediante iconos en Power BI | Datapeaker

Leave a Reply to Prachi Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: