Forum Discussion
Conditional Formatting when minimum and maximum are the same
I'm using the following Conditional Formatting colour scheme, but and I want greater control over what happens when the values are the same (in this instance, zero). As all the values are 0, I want it to display the Minimum colour, but it defaults to the Maximum.
I have tried wrapping the measure in an IF statement which converts 0 to BLANK(), but it still shows all values as Maximum.
Any help gratefully received
Don't worry about it, I've worked out a way around it for my specific use case. As I'm using percentages which won't go above 100 or below 0 I can hardwire it to this:
You might need to show the whole picture. For me, it works:
Measure = var _suma = SUM('Table'[Number]) RETURN IF(_suma = 0, BLANK(), _suma)Even If I only filter by 0 the number. The blanks always show orange like the rule.
I hope that helps,
11 Replies
Hi mcumis
You can manually control the colors by switching from "By color scale" to "By rules" in Conditional Formatting. Step-by-step fix using Rules:
1. Click on the visual where you want to apply Conditional Formatting.2. Go to the Format pane >> Data colors >> turn on Conditional formatting.
3. Change the format type from Color scale to Rules.
4. Add a rule like this:
If value is = 0, then use the desired Minimum color (e.g., light gray or white).
You can also define other rules if needed, like > 0 for a different color.
or
If you want to suppress zeros visually and only color when there's meaningful data, modify your measure like this:New Measure = IF([YourMeasure] = 0, BLANK(), [YourMeasure])Neither of these work for what I want to do. It's not a simple "0 = blue" and ">0 = red" question. I want a lowest to mid to highest gradient to apply, where the mid and high are not a threshold but relative. (As the gradient functionality does.)
Also as I said in my post, I tried wrapping it in IF and swapping 0 for blank() and it didn't work.
Hi. The alternative is the empty value. Can you show us how you are converting 0 to blank and how you are using the conditional format in a visual?
Regards
Thanks, 0 value isn't empty, it's the output of this measure:
=DIVIDE([a] ,DISTINCTCOUNT([b]))
I've tried various versions of this (" ", "", blank()) but none worked:FF11 (13 consecutive) % (People) = IF((DIVIDE([a] ,DISTINCTCOUNT([b])) = 0), "",DIVIDE([a] ,DISTINCTCOUNT([b])))Don't worry about it, I've worked out a way around it for my specific use case. As I'm using percentages which won't go above 100 or below 0 I can hardwire it to this:
