Forum Discussion

siddrow's avatar
siddrow
Helper III
2 years ago
Solved

Traffic light conditional formatting help

Hi

 

I created the below measure to use on the table visual in the cell element>font colour section. It doesn't allow me to put it in there (see below screenshot of red box that appears).

 

 

 

Due date, status and % complete are all columns from my dataset which comes from Sharepoint Lists, none are measures, calculated or custom columns.

 

Traffic Lights Colour =
    var _a= min('List'[Due Date])
    var _b= min('List'[Status])
    var _c= min('List'[% Complete])
return
    SWITCH(TRUE(),
    _a<=30 & _a>14 && _b="Not Started" && _c<=50,"#f58021",
    _a<=30 & _a>14 && _b="In Progress" && _c<=50,"#f58021",
    _a<=14 && _b="In Progress" && _c<=50,"#ee3224",
    _a<=14 && _b="Not Started" && _c<=50,"#ee3224",
    "#709302")
 
Basically trying to say:
 
if due date is equal to or less than 30 days but more than 14 days (from today) and status is "not started" and % complete is equal to or less than 50% then turn orange
 
if due date is equal to or less than 30 days but more than 14 days (from today) and status is "in progress" and % complete is equal to or less than 50% then turn orange
 
if due date is equal to or less than 14 days (from today) and status is "in progress" and % complete is equal to or less than 50% then turn red
 
if due date is equal to or less than 14 days (from today) and status is "not started" and % complete is equal to or less than 50% then turn red
 
else turn green
 
thanks in advance.
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi siddrow 
    It seems a syntax issue in the attached code :

    Try to modify it to :

    Traffic Lights Colour =
        var _a= min('List'[Due Date])
        var _b= min('List'[Status])
        var _c= min('List'[% Complete])
    return
        SWITCH(TRUE(),
        _a<=30 && _a>14 && _b="Not Started" && _c<=50,"#f58021",
        _a<=30 && _a>14 && _b="In Progress" && _c<=50,"#f58021",
        _a<=14 && _b="In Progress" && _c<=50,"#ee3224",
        _a<=14 && _b="Not Started" && _c<=50,"#ee3224",
        "#709302")

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

1 Reply

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi siddrow 
    It seems a syntax issue in the attached code :

    Try to modify it to :

    Traffic Lights Colour =
        var _a= min('List'[Due Date])
        var _b= min('List'[Status])
        var _c= min('List'[% Complete])
    return
        SWITCH(TRUE(),
        _a<=30 && _a>14 && _b="Not Started" && _c<=50,"#f58021",
        _a<=30 && _a>14 && _b="In Progress" && _c<=50,"#f58021",
        _a<=14 && _b="In Progress" && _c<=50,"#ee3224",
        _a<=14 && _b="Not Started" && _c<=50,"#ee3224",
        "#709302")

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly