Rules on conditional formatting

[Anonymous]

Hi Experts,

In conditional formatting for data colours, Less than (<) and Less then equal to (<=) are not shown.

pls help to get different colour for values less than zero 

Thanks

[v-jingzhang · Accepted solution]

Hi @Anonymous 

When you use Format by Rules, it requires a between range for every condition. This is by design currently. If you are sure that a very small negative value will not be reached in your case, you can set it as the start number of the range less than zero. For example, if value > -1000000 and < 0 then "color code".

Otherwise, if negative values are possible to reach very very small that you are not sure, you can use Format by Field Value. You can use DAX to create a measure to decide the colors, just like Amit suggested. You can use either Switch or If statement to create custom conditions. Here is an exmple with IF conditions Conditional formatting by field value in Power BI

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@Anonymous , Because this first value, It is expected to between range.

Another way is to create a color measure and use that is conditional formatting using field value option

example

switch(true() ,
max(Table[status]) = "Completed" , "Green",
max(Table[status]) = "Running" , "Grey"
max(Table[status]) = "Errored" , "Red"
)

Switch( True() ,
[Percent] =1 , "Green"
[percent] <.75 , "red",
[percent] <.99 , "Yellow"
)

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4