PowerBI Report Builder (Paginated) Switch Expression Issue

[Peppy_Le_Dew]

Hi All.  This question/issue is concerning PowerBI Report Builder (or Paginated reports).  Didn't see a sub-forum for that service so posting it here in Desktop.

My issue is that I have an expression which uses SWITCH to apply a background color to tablix cell based on some fields value.  What's weird is this expression seems to work 90% of the time, but there are some outliers which aren't working.

Here is the expression:

And here is a snip of the paginated report.  You can see it works for On Track, and (not shown below) it is also working for 'At Risk'.  But for this one 'Blocked' cell it doesn't seem to be working.

Adding to my confusion, I am using almost the same expression to populate the text to these cells in the first place, and as we can see this piece is working since the cell shows as 'Blocked'

This probably isn't enough context to point me straight to the issue, but wondering if anyone has ideas on what might cause this, or where I can check in my report.  Help is appreciated, thanks in advance!

[rohit1991]

Hi @Peppy_Le_Dew 

The issue you're experiencing in Power BI Report Builder with the SWITCH expression not applying the expected background color to certain values like "Blocked" is most likely due to hidden data inconsistencies, such as trailing or leading spaces, case mismatches, or even invisible characters in your dataset.

Although the text "Blocked" may appear correct visually, the actual value in the field could include extra spaces or different casing (e.g., "Blocked ", " blocked", or similar), which causes the SWITCH expression to fail at matching it. To resolve this, it's recommended to wrap the field in TRIM() to remove any extra spaces and UCASE() or LCASE() to normalize casing within your expression.

Additionally, always include a default case in your SWITCH statement to handle unmatched values gracefully. For example, using SWITCH(UCASE(TRIM(Fields!Status.Value)), "ON TRACK", "Green", "AT RISK", "Yellow", "BLOCKED", "Red", "Transparent") ensures consistent results.

You can also create a temporary output to display the exact string and its length to visually confirm any hidden characters. These measures should help ensure that your conditional formatting applies correctly across all intended values.

[SreeniBattula]

# is missing in the color code "e81224" in the expression. it  should be "#e81224"

[GeorgeColl]

Appreciate this is beyond late, but I stumbled across this looking for some examples using the switch function and this was a top result. I noticed the issue so thought may aswell give the answer.

The hex value for the "Blocked" row was missing a # sign at the start of the hex value, hence not working for that one but working for the others.