Forum Discussion

Kumar_0606's avatar
Kumar_0606
Icon for Helper I rankHelper I
1 year ago
Solved

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 tr...
Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    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])

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
1 year ago

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's avatar
Kumar_0606
Icon for Helper I rankHelper I
1 year ago

Thanks Miguel.

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

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    1 year ago

    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.