Forum Discussion
akwang
8 years agoAdvocate II
DAX Nested IF
Hi all. I am trying to calculate a a column with a Nested IF staement as below: Job Year Category = IF(Employee_Details[Job Years]<=5,"1-5 Years", IF(Employee_Details[Job Years]>=6<10,"6-10...
- 8 years ago
Starting with your second IF statement, you have a double comparison going on, which is not valid:
IF(Employee_Details[Job Years] >= 6 < 10, "6-10 Years",
DAX is essentially seeing the Employee_Details[Job Years] >= 6 as a TRUE/FALSE value, and then using that to compare against the integer 10. If you want to use this pattern, you'll need to use conditional logic (AND) like so:
IF( AND( Employee_Details[Job Years] >= 6, Employee_Details[Job Years] < 10 ), "6-10 Years", ... or.. IF( Employee_Details[Job Years] >= 6 && Employee_Details[Job Years] < 10, "6-10 Years", ...
Ultimately, you may want to consider making use of the SWITCH statement to make things more legible.
SWITCH(TRUE(), Employee_Details[Job Years] <= 5, "1-5 Years", AND(Employee_Details[Job Years] >= 6, Employee_Details[Job Years] < 10), "6-10 Years", AND(Employee_Details[Job Years] >= 11, Employee_Details[Job Years] < 15), "11-15 Years", AND(Employee_Details[Job Years] >= 16, Employee_Details[Job Years] < 20), "16-20 Years" ... and so on.
Anonymous
8 years agoNot applicable
Might be a silly question, but have you tried just using the "Group Function" in the data view? You could group the Job Years through 30 and rename the "Other" group to "Above 30 Years"