Conditional formatting with text

[AMcMasterSage]

Hi All,

I'm very new to using PowerBI so I may need a 'For Dummies' explanation here, but essentially what I'm trying to do is a traffic light status for the below pictured table;

The idea is that the traffic light should be red if everything in the row (Save the year and month) reads 'Not Started', and should be green if everything in the row reads 'Approved by FD'. Anything else should show the light as yellow. I've had a go at trying to do this but I'm not really certain on how you would go about doing this. Any advice or steps is appreciated, thank you.

[jaideepnema · Accepted solution]

Hi @AMcMasterSage ,

Create a calculated column like this:

Status = SWITCH(TRUE(),

Data[Canada]="Approved by FD" && Data[France]="Approved by FD" && Data[Germany]="Approved by FD" && Data[Portugal]="Approved by FD" &&Data[South Africa]="Approved by FD" && Data[Spain ]="Approved by FD" &&Data[USA]="Approved by FD" &&Data[UK]="Approved by FD",1,

Data[Canada]="Not Started" && Data[France]="Not Started" && Data[Germany]="Not Started" && Data[Portugal]="Not Started" &&Data[South Africa]="Not Started" && Data[Spain ]="Not Started" &&Data[USA]="Not Started" &&Data[UK]="Not Started",2,

3)

And apply conditional formatting on this column as shown below:

This will give you the expected output:

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[Jhadur]

@jaideepnema I am looking for a similar solution but I need it to be OR not AND. How would this be changed to use an OR statement? I modified the above for my columns but if the first cell is not Y or N then the conditional formatting doesn't apply to the whole row.

[amitchandak]

@AMcMasterSage , You should be able to create is using unichar and color measure

use unichar of circle (https://exceleratorbi.com.au/dax-unichar-function-power-bi/
)  as measure and in conditional formatting use a measure like one below in field value

/////Arrow
/////Arrow Color
Arrow color =
var _change =max(Status)
return
SWITCH (
TRUE(),
"Not Started "> 0, "green",
"Approved by FD" = 0, "Yellow",
 "red"
)

if you need arrow

Arrow =
var _change =[Net Sales YTD]-[Net Sales LYTD]
return
SWITCH (
TRUE(),
_change > 0, UNICHAR(9650),
_change = 0, UNICHAR(9654),
_change < 0, UNICHAR(9660)
)

refer

UNICHAR - Tool for Custom Icon Formatting: https://www.youtube.com/watch?v=veCtfP8IhbI&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=50