Conditional If in DAX didnt work

[pauline_teo]

Hi, 

I am trying to group staff's tenure into 5 main categories : Less than 1 year, Between 1 and 3 years, Between 3 and 5 years and Between 5 and 10 years. To do this, I created a new column and came up with the following DAX formula 

Tenure (Cat.) = if(Stafflist[Tenure]>10,"More than 10 yrs",if(Stafflist[Tenure]>5,"Between 5 and 10 yrs",if(Stafflist[Tenure]>3,"Between 3 and 5 yrs",if(Stafflist[Tenure]>1,"Between 1 and 3 yrs","Less than 1 yr")))) . The formula resulted in the following 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."  Can anyone decode what this error message means and how can I resolve the issue? 

[lbendlin]

Independent of the other replies - check out the SWITCH(TRUE()) syntax sugar that makes your nested IF statements look a bit better

SWITCH(TRUE(),Stafflist[Tenure]>10,"More than 10 yrs",Stafflist[Tenure]>5,"Between 5 and 10 yrs",Stafflist[Tenure]>3,"Between 3 and 5 yrs",Stafflist[Tenure]>1,"Between 1 and 3 yrs","Less than 1 yr")

[harshnathani]

Hi @pauline_teo  ,

Please check format of Stafflist [Tenure]. 

Change it from text to Whole Number.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[pauline_teo]

Got it. Thanks! 

[avatorl]

@pauline_teo 

The message means what it says. You're comparing text value with numeric value. And because all comparisions in your DAX are similar Stafflist[Tenure]>{numeric value}, then the only place in a comparison where for a text value is Stafflist[Tenure] field. Verify the Stafflist[Tenure] column and make sure you changed it's data type to Whole number.