topic Re: Conditional Formatting for values not equal to 0 is not working properly in DAX Commands and Tips

Conditional Formatting for values not equal to 0 is not working properly

zyeet — Fri, 22 Dec 2023 08:03:56 GMT

I am trying to highlight the Discrepancies in my data where the values not being equal to 0 should be highlighted red. But the problem is that some rows that have the value 0, are also being highlighted.

The rule I wrote is:

The Conditional formatting shows up like this:

Re: Conditional Formatting for values not equal to 0 is not working properly

Jihwan_Kim — Sat, 23 Dec 2023 04:01:00 GMT

Hi, I am not sure how your semantic model looks like, but please try using ROUND DAX function whether it suits your requirement.

ROUND function (DAX) - DAX | Microsoft Learn

Re: Conditional Formatting for values not equal to 0 is not working properly

Anonymous — Mon, 25 Dec 2023 03:12:58 GMT

Hi zyeet

Based on the information you provided me, I think the problem may be in -1e+3!, you can change it to a numeric type like -1000 so that there is no problem associated with it.

Turn -1e+3! into -1000.

2. All 0.00 are become the same color.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Conditional Formatting for values not equal to 0 is not working properly

zyeet — Thu, 28 Dec 2023 06:46:32 GMT

I have tried your solution but not all my 0's are being formatted green:

You can check the pbix file here:

Draft Comparison Report.pbix

Re: Conditional Formatting for values not equal to 0 is not working properly

Anonymous — Mon, 22 Jan 2024 08:54:07 GMT

Hi, zyeet

Based on the information you have provided, Here are my answers to your questions.

I opened your pbix file and found that there were indeed some 0.00s that didn't meet the green condition, so I rewrote a Color's Measure that returns green when the value of Variance is not equal to 0, and what I got was also that there were a lot of rows of 0.00s that turned green, which indicates that these values are not equal to 0.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Conditional Formatting for values not equal to 0 is not working properly

WranglerPro_BDT — Sat, 25 May 2024 02:46:00 GMT

Found a workaround and hope this is helpful:

Pick a color for zero, then set the same colour for a range that accomodates rounding errors: MIN (-0.01) and MAX (0.01)

Then pick the colour you want for reconciling items and start from the boundaries set for zero so it looks like this: