Conditional Formatting not working on all rows

[chrisbrigg]

Hello,

Using Power BI Report Server (Sept 2019) I recently set up a calculated measure called Variance:

CALCULATE(SUM('ACCT_BAL1'[ACCT_BAL]) - SUM('ACCT_BAL2'[ACCT_BAL]))

ACCT_BAL field is formatted as Decimal Number in both tables.

I then set Conditional Formatting on this meaure formatted by Rules based on field Variance:

"If value is 0 number then green background color"

The problem is the Conditional Formatting only works on some of the values equal to zero. Clearly there are other Variance measure values equal to zero but the formatting does not apply.

Has anyone else experienced this?

Conditional Formatting results

[d_gosbell]

Decimal is a floating point number so it's possible that the actually value is something like 0.00000000001 and when you've formatted it to 2 decimal places it rounds it down to 0.00. If you change your condition so something like "less than 0.004" does that work?

[chrisbrigg · Accepted solution]

This issue came up again recently when the customer asked to see decimal numbers instead of whole numbers. The conditional formatting was again not applying to the correct condtions. For example, my calculated mesaure (deimal number) displayed 0.00 but the conditional formatting did not pick up on it.

Going back to @d_gosbell statement about the foating decimal, I found that even though the calculated measure displayed "0.00", when I opened up the number of decimal places to 15, I found that true number to be "0.000000000400000". This stemmed from the two source columns used in the calculated measure, which were formatted as 'Decimal Number' in the Query Editor.

I changed the two source columns used in my calculated measure in Query Editor to Fixed Decimal, which solved my problem. All of the flaoting numbers in the Decimal format were removed and the condtional formatting applies correctly. Took me way longer to figure this out than I'd like to admit but I hope it helps others.

[SamiP]

I finally discovered this is caused by some columns being set to somethign other than 'Don't Summarize'.

In circumstances where the columns to be summarized are blank, this causes an error, which somehow causes the conditional formatting to fail (even where the formatting is being done on another column). Clearly an error within the application.

[d_gosbell]

---

@SamiP wrote:

I finally discovered this is caused by some columns being set to somethign other than 'Don't Summarize'.

In circumstances where the columns to be summarized are blank, this causes an error, which somehow causes the conditional formatting to fail (even where the formatting is being done on another column). Clearly an error within the application.

---

I just tried to reproduce this and I cannot, the conditional formatting works fine for me regardless of the presence of any blanks in an aggregated column. Are you able to reproduce this in an example file that you can share?

[chrisbrigg]

Thank you for the reply, @d_gosbell . To test your theory I set the Cond. Formatting rule to:

If value is greater than 0 (num) and is less than 1 (num)

Unfortunately, the cond. formatting still does not apply to all calculated measure values equalling '0.00'. The cond. formatting is only applied to some calculated measure values equalling '0.00'. I am trying to determing if there is a pattern in those not being formatted.

[chrisbrigg]

When reviewing my calculated measure, I realized it was created under a Home Table holding dimension data, not fact data. I changed the Home Table location of the cal. mesure to one of the tables holding the fact data and now the Conditional Formatting works. Does this make sense?

[chrisbrigg]

After further developing the same report, I encountered the same problem with conditional formatting not applying to my calculated measure.

To get everything working again, I had to change both decimal numbers fields used in the calculated measure to whole numbers in my queries. This may relate back to @d_gosbell reply but I cannot say for sure. It definitely has something to do with the number formatting. I believe I did this before my earlier reply but thought the fix was due to the Home Table of the measure.