Forum Discussion
How to create Calculated column with both Text and Number comparison
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
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])
3 Replies
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])Thanks Miguel.
I checked the year column, data type is Text so i enclosed year value in quotes in your formula.
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.
