Forum Discussion

akwang's avatar
akwang
Advocate II
8 years ago
Solved

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...
Need Help
  • malagari's avatar
    malagari
    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.
malagari's avatar
malagari
Continued Contributor
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.
  • PowerBINewbie_1's avatar
    PowerBINewbie_1
    Helper I
    8 years ago

    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