Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
simple_test_1
Employee
Employee

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 🙂 

View solution in original post

5 REPLIES 5
V-pazhen-msft
Community Support
Community Support

@simple_test_1 
Those aggregation type do not effect the result in conditional formatting, just selected any aggregation type.

conditonal formating.PNG

 

 

Paul Zheng _ Community Support TeamIf this post helps, please Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

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

Anonymous
Not applicable

Having that column as string type complicates it a little bit more. If it's possible for you, send me a sample or your .pbix file and I can help you 🙂 

It looks like Option B did the trick after a refresh. The string type ended up being find so long as you say in your if statement the value ="0" instead of 0. If anyone else is having trouble it does see the aggregation type (at least for field value conditional formatting) doesn't really have an effect. If you see if mishighlighting, just try to refresh your application.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.