Forum Discussion

AMcMasterSage's avatar
AMcMasterSage
New Member
4 years ago
Solved

Conditional formatting with text

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.

Need Help
Tutorial Requests
  • jaideepnema's avatar
    jaideepnema
    4 years ago

    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 ğŸ˜€

     

3 Replies

  • jaideepnema's avatar
    jaideepnema
    Solution Sage
    4 years ago

    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's avatar
      Jhadur
      Helper I
      2 years ago

      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's avatar
    amitchandak
    Super User
    4 years ago

    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