DAX: how to assign color hex code based on text value?

1
votes

I am trying to change the color of a text column based on the text itself:

  • VERY GOOD: green
  • GOOD: light green
  • FAIR: yellow
  • BAD: orange
  • VERY BAD: red

I wrote the following DAX expression:

RAG = 
     VAR Category = SELECTEDVALUE('Data'[Text]) 
     RETURN SWITCH(Category = "VERY GOOD", "#41AC4C", 
                   Category = "GOOD", "#AFC236", 
                   Category = "FAIR", "#FFD400", 
                   Category = "BAD", "#E95A1A", 
                   Category = "VERY BAD", "#E42925")

However, applying this function in Power BI returns the error message:

Error Message: MdxScript(Model) (6, 148) Calculation error in measure 'Data'[RAG]:

Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

What am I doing wrong?

These are my version details:

Feedback Type:
Frown (Error)

Release:
September 2020

Product Version:
2.85.681.0 (20.09) (x64)

OS Version:
Microsoft Windows NT 10.0.18363.0 (x64 en-GB)

CLR Version:
4.7 or later [Release Number = 528040]
1
powerbiswitch-statementdaxconditional-formatting

1 Answers

2
votes

You are not using the SWITCH function the right way, check out the documentation: SWITCH fuction DAX

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

This is how it should be written:

RAG = 
     VAR Category = SELECTEDVALUE('Data'[Text]) 
     RETURN SWITCH(Category, 
                   "VERY GOOD", "#41AC4C", 
                   "GOOD", "#AFC236", 
                   "FAIR", "#FFD400", 
                   "BAD", "#E95A1A", 
                   "VERY BAD", "#E42925"
                   )