Forum Discussion
PowerBI Report Builder (Paginated) Switch Expression Issue
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!
3 Replies
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.
# is missing in the color code "e81224" in the expression. it should be "#e81224"
Hi Peppy_Le_Dew
SWITCH is mostly working but randomly fails on something like “Blocked,” chances are it’s not the expression it’s the data.
A few things to check:
- Trailing spaces : the value might look like “Blocked” but actually be “Blocked ” (with a space).
- Case sensitivity : SWITCH is picky. “Blocked” ≠ “BLOCKED”
- Weird characters : I once had a value fail because of a hidden non-breaking space copied from Excel.
What worked for me was wrapping the field in TRIM() and sometimes even UCASE() like:
SWITCH(TRIM(UCASE(Fields!Status.Value)),
"BLOCKED", "Red",
"ON TRACK", "Green",
"AT RISK", "Yellow",
"GRAY"
)
Also, add a default return just in case it doesn’t match anything.
Lastly to debug it, just drop the raw field into a text box on the report and see what it actually says at runtime. That’ll usually tell you what’s up.
