How to create Calculated column with both Text and Number comparison

[Kumar_0606]

Hello Experts, 

I have a formula like this

IF [Category]='Plan' THEN

IF [year]>=2020 AND [Code]= 'USD123' THEN [Measure]
ELSE

IF [year]<2020 AND [Code]='USD456'

THEN [Measure]
END

END

I tried using IF statement but getting the error " DAX comparison operations do not support comparing values of type text with values of type integer"

Any help would be greatly appreciated.

Thanks

[MFelix · Accepted solution]

Hi @Kumar_0606 

Since you are using several IF the best option is to use a switch statment however your problem saying the it does not allow to compare values it seems to me that one of those columns has the incorrect format, check the format of the Year ccolumn to see if it's a number and try the following measure:

SWITCH( TRUE(),
Table[Category]="Plan"  && Table[year]>=2020 && Table[Code]= 'USD123', [Measure],
Table[Category]="Plan"  && Table[year]<2020 && [Code]='USD456', [Measure])

[Kumar_0606]

Thanks Miguel.

I checked the year column, data type is Text so i enclosed year value in quotes in your formula.

[MFelix]

Hi @Kumar_0606 ,

Glad it worked, but I suggest that you change the year column to a Number format because it will be more efficient.