Forum Discussion
Conditional Formatting on a per value basis in table
Is it possible to highlight cells in my table on conditions such as "if this number is greater than 0, highlight red, else don't highlight." When I try to do this, I only see conditional formatting based on aggregate data "if this columns average is greater than 0" not "if this specific value in this column is greater than 0." Am I missing something?
You can achieve this by different simple options.
Option A:
Apply the conditional format by rules as you mention it. Since data will be on a table, aggreation won't matter because data will be at a row level thus you will have the cell value. That's the easiest one.
Option B:
Go to Power Query and add a conditional column where" if column with x value is greather than 0 then:" you add the HEX code for red (#FF0000) else white (#FFFFFF).
Then, apply conditional format by field value and select the column with the HEX codes.
Option C:
Create a DAX measure with the following structure:
MeasureForFormat =
var cell_value = SELECTEDVALUE([ColumnWhereYourNumberIs])
Return
IF(cell_value > 0, "#FF0000", "#FFFFFF")
Then, apply conditional formmating by field value and use this measure as field value.
I hope this helps 🙂
5 Replies
You can achieve this by different simple options.
Option A:
Apply the conditional format by rules as you mention it. Since data will be on a table, aggreation won't matter because data will be at a row level thus you will have the cell value. That's the easiest one.
Option B:
Go to Power Query and add a conditional column where" if column with x value is greather than 0 then:" you add the HEX code for red (#FF0000) else white (#FFFFFF).
Then, apply conditional format by field value and select the column with the HEX codes.
Option C:
Create a DAX measure with the following structure:
MeasureForFormat =
var cell_value = SELECTEDVALUE([ColumnWhereYourNumberIs])
Return
IF(cell_value > 0, "#FF0000", "#FFFFFF")
Then, apply conditional formmating by field value and use this measure as field value.
I hope this helps 🙂
First it should be noted that while my columns mostly represent numbers, they are of string data types incase a 'NA' value comes in. I don't know if that makes a different in this situation, I hope not.
Option A:Testing has shown this not to be the case. The aggreation options I have are count and count (distinct). I tried condtional formatting by rules, and then "greater than 0" and "less than 999999999." then Red. And this painted cells that had 0 in them red.
Option B:
I made a column called X_color:
X_Color = IF( Table[X]="0", "#FFFFFF", #FF0000")
for each column I want this to happen to.
Then I did conditional formatting, field value, based on X_Color, and my aggregation is First or Last, I chose first. This means that if I have a column with 2 non-zero values, it only colors the first one. For example, I have a column of all zeros, it does fine, a column of all zeros but one value that is non-zero, it does fine, then a column with two non-zero values, and it only highlights one of the non-zero values.
Option C:
I fear I will meet with the same problem from Option B if I follow this.
Any other options? Is there a reason why I have to choose an aggregation in the first place? Seems like a painful limitation of PowerBI
simple_test_1
Those aggregation type do not effect the result in conditional formatting, just selected any aggregation type.Paul Zheng _ Community Support TeamIf this post helps, please Accept it as the solution to help the other members find it more quickly.
