Help with Switch Error

[rosamhernandez1]

Working on getting a traffic light set up, have a column set up to determine the date difference between two dates. Now need to convert that number into a color, this is what I had set up but getting error message. ("DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.")

This is the data: 

Project Name	Approved ISD	Projected ISD	SchedNumDays	SchedHealth
1	12/31/2025	12/31/2025	0	Green
2		2/28/2025		Green
3	3/31/2025	3/31/2026	365	Red
4	9/30/2025	9/30/2025	0	Green
5	3/31/2026	3/31/2026	0	Green
6		12/31/2026		Green
7	9/30/2025	9/30/2025	0	Green
8	12/31/2024	2/15/2025	46	Yellow
9	9/30/2025	9/30/2025	0	Green
10	12/31/2024	12/31/2024	0	Green
11	9/30/2025	9/30/2025	0	Green
12		12/31/2026		Green
13		3/1/2027		Green
14	8/30/2024	1/21/2025	144	Red
15	9/30/2024	9/30/2024	0	Green

and here is my SWITCH statement:

SchedHealth = SWITCH(True(),

'Active Projects'[SchedNumDays]="","Green",

'Active Projects'[SchedNumDays]>90,"Red",

'Active Projects'[SchedNumDays]<=0,"Green",

'Active Projects'[SchedNumDays]<90,"Yellow")

[HotChilli · Accepted solution]

I'm confused by the first sentence.

It looks to me that you want to treat it as a number, so it should be a number datatype.  However, the error is caused by using two different types of comparison: one string comparison and one number comparison.

'Active Projects'[SchedNumDays] = ""
'Active Projects'[SchedNumDays] > 90

--

I think if I was you I would convert the datatype to number and alter the switch statement to test for BLANK or null or whatever these empty values get converted to.

[rosamhernandez1]

thank you! that worked, apologies on being confusing. 

[HotChilli]

I'm talking about the DAX code. There is a difference on one of the comparison cases.

The error message is giving you the solution to deal with it.

[rosamhernandez1]

got it, the column was not created with the incoming data so I can't find it in the query. I have to recreate the columns in the query so that I can set them to the correct data type, correct?

[HotChilli]

Look at the values you are comparing in each case. Are they the same datatype or are they different datatypes?

[rosamhernandez1]

both columns are Text data type