Forum Discussion

siddrow's avatar
siddrow
Icon for Helper III rankHelper III
2 years ago

Switch traffic light colour

Hi

 

I am trying to switch the colours based off mulitple criteria but I am stuck.

 

I created my traffic lights.

 

Traffic Lights = UNICHAR(11044)

 

I have created this measure to calcualte the number of days between the due date and today.

 

Due Date Days from Today = DATEDIFF(TODAY(),MAX('List'[Due Date]),DAY)
 
I have then created this measure to switch the colours, however my % Complete and Status columns are not measures, they are normal power query columns. Each row in both of those columns will have different values and text, so I can't use MIN/MAX etc. I need each row for those columns to be evaluated and then the traffic light changed colours based off the below criteria.
 
Traffic Lights Colour = Switch( True(),
[Due Date Days from Today] <=30 && [Due Date Days from Today] >14 && [% Complete] <=.50 && [Status] = "Not Started", "#f58021",
[Due Date Days from Today] <=30 && [Due Date Days from Today] >14 && [% Complete] <=.50 && [Status] = "In Progress", "#f58021",
[Due Date Days from Today] <=14 && [% Complete] <=.50 && [Status] = "In Progress", "#ee3224",
[Due Date Days from Today] <=14 && [% Complete] <=.50 && [Status] = "Not Started", "#ee3224",
"#709302")
 
How can I replace the % Complete and Status columns so this measure works? Or do I need to do this in Power Query instead?
 
 thanks in advance.

 

Need Help

4 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi siddrow ,

     

    You can try to create this Due Date Days from Today as a column and then Traffic Lights Colour creates this as a column.

    Please let me know if you have already tried this and what was the issue.

     

    • siddrow's avatar
      siddrow
      Icon for Helper III rankHelper III
      2 years ago

      Sorry, could you please add more info? It's not making sense to me. Are you saying created a custom column in Power Query for Due Date Days from Today? If so, I can't use this column in my measure either. 

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion
        2 years ago

        siddrow I am suggesting not to create any measure and go ahead with creating columns for both Due Date Days from Today and Traffic Lights Colour. You don't need to create it in power query you can create it directly after the data load.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi siddrow ,

     

    Since this is a normal field, why can't you use the MAX/MIN function to get this row. Please share your sample data and the expected results.

     

    Best regards,
    Community Support Team_ Scott Chang