Conditional Formatting based on Text

[Anonymous]

Hi, i have a "status rag" field that is either reg, amber or green.

I wish to have a card (or possibly table field?) that changes colour dependent on the value. I have tried 'cards with states' by OKviz, but this appears to only work with numeric values, as does conditional formatting in tables.

So have tried to create a measure that returns a numeric value,-

zzStatusToNumeric = 

Switch ( True(),

 'Projects (2)'[StatusRAG] = "Red","1" ) 

but have received an error:-

This is the field i am trying to reference:-

Note thet there is a slicer on for 'project' so there is only one relevant row on the table. I think i need to limit the measure to FIRST (or similar) but am struggling...

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

First, you should know the difference between measure and column.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

A measure operates on aggregations of data defined by the current context
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for you requirement, you could try this way

Use SELECTEDVALUE in your formula, so adjust your measure like this:

zzStatusToNumeric = 

Switch ( True(),

SELECTEDVALUE( 'Projects (2)'[StatusRAG] )= "Red","1" ) 

or add a "zzStatusToNumeric column" then Conditional Formatting based it by the same logic.

Best Regards,
Lin

[Anonymous]

Thanks Lin. Apologies for the deay in replying but we decided to park this requriement, but we are now back trying to get this working.

A couple of questions. 

• is the formula you gave for a measure or column?
• You mention that "measure operates on aggregations of data". As the project table will be filtered to a single row by a slicer, do i need to aggregate as i am returning a single field in a row (the RAG of a project, the project table is 1 row per project)
• I have created the following measure

  zzStatusRAGtoNumeric = 
  Switch ( True(),
  SELECTEDVALUE( 'Projects (2)'[StatusRAG] )= "Red","1" ,"Amber", "2", "Green" , "3")

• I have then added a column to give a solid circle to conditionally format

• zzStatusRAG_UNICHAR_Column = UNICHAR(11044)

• And attemptted to conditional format this column based on  zzStatusRAGtoNumeric 
• But get the following error:-

• Feedback Type:
  Frown (Error)
  
  Timestamp:
  2019-07-30T09:28:35.0326414Z
  
  Local Time:
  2019-07-30T10:28:35.0326414+01:00
  
  Session ID:
  56322f8b-fde1-4de1-b539-95c572e00cf8
  
  Release:
  June 2019
  
  Product Version:
  2.70.5494.761 (19.06) (x64)
  
  Error Message:
  MdxScript(Model) (27, 70) Calculation error in measure 'Projects (2)'[zzStatusRAGtoNumeric]: 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.
  
  
  
  

  Any ideas what i am doing wrong? @v-lili6-msft