Forum Discussion
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 Years",
IF(Employee_Details[Job Years]>=11<15,"11-15 Years",
IF(Employee_Details[Job Years]>=16<20,"16-20 Years",
IF(Employee_Details[Job Years]>=21<25,"21-25 Years",
IF(Employee_Details[Job Years]>=26<30,"26-30 Years",
"Above 30 Years"))))))
I have a column Job Year which shows the amount Years of service for an Employee and i want to create a graph based on the Ranges above
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.
3 Replies
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.
Malagari,
I'm working on a similar issue such as the one you've answered in this post. I've tried to apply your solution and apparently, the TODAY() function does not allow me to process my calculation. Would you be able to assist me with a solution too?
Here's a link to my original post: http://community.powerbi.com/t5/Desktop/Nested-DAX/m-p/412871/highlight/false#M189486
Here is what I came up with after applying your logic to my issue:
SWITCH (TRUE()
[SharePointColumnA] = "", 0,
AND [SharePointColumnD] = "",CALCULATE (SUM(DateTable[WORKDAY]), DATESBETWEEN (DateTable[Date], AND'SharePointList'[SharePointColumnA], TODAY(),
AND
CALCULATE(SUM(DateTable[WORKDAY]),DATESBETWEEN(DateTable[Date],'SharePointList'[SharePointColumnA],'SharePointList'[SharePointColumnD])))
ERROR MESSAGE: Too few arguments were passed to the AND function. The minimum argument count for the function is 2.
Could you help me identify what I am doing wrong? Per my post, I have 27 additional columns to apply this logic to. Only, I will have different expressions.
Thanks,
PBN
