Conditional Formatting when minimum and maximum are the same

[mcumis]

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

[mcumis · Accepted solution]

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:

[ibarrau]

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

[mcumis]

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])))

[ibarrau]

Just for the heads up. An empty in dax is a BLANK(). IF it wasn't working maybe it would be a matter of debuging the result of the division.

I'm glad you found a solution.

[mcumis]

Thanks, that might be so, but converting 0 to blank() via the IF statement wasn't solving the issue. It cleared out the values but still formatted to the maximum colour (perhaps because the MAX values overrides everything, even blanks?).

[ibarrau · Accepted solution]

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,

[mcumis]

This did work, I must have made a mistake yesterday. Thanks for taking the time to reply.

[mcumis]

Thanks, it's this:

 and the table looks like this:

changing the 0.0% to blank() doesn't make a difference, because all the values in the range are blank() and it seems to work on the logic that if highest and lowest are equal, use the formatting for highest.

As per that example, if I use 0% as minimum, and 100% as max then 0.7%, 1.2%, 1.9% and 2.3% are all low values.

[mcumis · Accepted solution]

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:

[mcumis]

I've changed the status as this isn't the solution. It works for 0 values, but skews all the others by implying that 100% should be at the top of the divergence and 0% at the bottom. Which means that if the range is only 1%-4% it will show all the values as "good", when I want the top value to show as "bad" irrespective of whether it's 4% or 74%.

[mcumis]

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.

[rohit1991]

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])