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:

image

…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:

image

…then choose to format by rules. You see this dialog:

image

In particular, the part of this dialog where you set up the rule:

image

…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:

image

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:

image

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:

image

(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:

image

And here’s the output:

image

10 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 | Pardaan.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

%d bloggers like this: