Forum Discussion
DAX Nested IF
- 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.
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.