Switch traffic light colour

[siddrow]

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.

[v-tianyich-msft]

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

[Samarth_18]

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]

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]

@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.