Forum Discussion

Stuznet's avatar
Stuznet
Helper V
7 years ago
Solved

Nested IF/AND Statement

I got stucked writing Nested IF/AND statement.    Below is the statement I wrote in Excel, ref!A1 is the date 8/31/2018   =IF(V2>ref!$A$1,"Future",IF(AND(V2<=ref!$A$1, X2=0),"Late",IF(AND(V2<=ref...
Need Help
Show and Tell
  • Sean's avatar
    Sean
    7 years ago

    The reason your formula is not working is that AND accepts only 2 arguments!

    I personally prefer MFelix's SWITCH approach using && easier to ready (for me at least)

    But you could try this...

    Column =
    IF (
        Data[BL Date] > DATE ( 2018, 8, 31 )
            = "Future",
        IF (
            AND ( Data[BL Date] <= DATE ( 2018, 8, 31 ), Data[Actual Date] <> 0 ),
            "Late",
            IF (
                AND ( AND ( Data[BL Date] <> 0, Data[Actual Date] <> 0 ), Data[Variance] <= 0 ),
                "On-Time",
                "Late"
            )
        )
    )

    StuznetLet me know if this actually works! :smileyhappy:

     

    EDIT: MFelix I believe you need to add this condition...

    nested if =
IF (
    Data[BL Date] > DATE ( 2018, 8, 31 ),
    "Future",
    IF (
        Data[BL Date] <= DATE ( 2018, 8, 31 )
            && Data[Actual Date] <> 0,
        "Late",
        IF (
            Data[BL Date] <> 0
                && Data[Actual Date] <> 0
                && Data[Variance] <= 0,
            "On-Time",
            "Late"
        )
    )
)

    And...

    SWITCH FORMULA =
SWITCH (
    TRUE (),
    Data[BL Date] > DATE ( 2018, 8, 31 ), "Future",
    Data[BL Date] <= DATE ( 2018, 8, 31 )
        && Data[Actual Date] <> 0, "Late",
    Data[BL Date] <> 0
        && Data[Actual Date] <> 0
        && Data[Variance] <= 0, "On-Time",
    "Late"
)

     

  • Stuznet's avatar
    Stuznet
    7 years ago

    Thank you so much for your help but the correct formula I'm looking is this. Now the result are matching. 

     

    Spoiler
    SWITCH FORMULA =
    SWITCH (
    TRUE (),
    Data[BL Date] > DATE ( 2018, 8, 31 ), "Future",
    Data[BL Date] <= DATE ( 2018, 8, 31 )
    && ISBLANK(Data[Actual Date]) , "Late",
    Data[BL Date] <= DATE ( 2018, 8, 31 )
    && ISBLANK(Data[Actual Date]) = FALSE()
    && Data[Variance] <= 0, "On-Time",
    "Late"
    )

     

MFelix's avatar
MFelix
Super User
7 years ago

Hi Stuznet,

 

You have some incorrections on DAX IF you don't use the = to define the states also the brackets I have made some changes to reduce the DAX you can use one of both formulas below:

 

nested if =
IF (
    Data[BL Date] > DATE ( 2018, 8, 31 ),
    "Future",
    IF (
        Data[BL Date] <= DATE ( 2018, 8, 31 )
            && Data[Actual Date] <> 0,
        "Late",
        IF (
            Data[BL Date] <> 0 
                && Data[Actual Date] <> 0
                && Data[Variance] <= 0,
            "On-Time",
            "Late"
        )
    )
)

 

Rather than use the nested IF is better to use the SWITHC function check the documentation here, see below the dax formula for your case.

SWITCH FORMULA =
SWITCH (
    TRUE (),
    Data[BL Date] > DATE ( 2018, 8, 31 ), "Future",
    Data[BL Date] <= DATE ( 2018, 8, 31 )
        && Data[Actual Date] <> 0, "Late",
    Data[BL Date] <> 0
        && Data[Actual Date] <> 0
        && Data[Variance] <= 0, "On-Time",
    "Late"
)

Regards,

MFelix

 

Sean's avatar
Sean
Community Champion
7 years ago

The reason your formula is not working is that AND accepts only 2 arguments!

I personally prefer MFelix's SWITCH approach using && easier to ready (for me at least)

But you could try this...

Column =
IF (
    Data[BL Date] > DATE ( 2018, 8, 31 )
        = "Future",
    IF (
        AND ( Data[BL Date] <= DATE ( 2018, 8, 31 ), Data[Actual Date] <> 0 ),
        "Late",
        IF (
            AND ( AND ( Data[BL Date] <> 0, Data[Actual Date] <> 0 ), Data[Variance] <= 0 ),
            "On-Time",
            "Late"
        )
    )
)

StuznetLet me know if this actually works! :smileyhappy:

 

EDIT: MFelix I believe you need to add this condition...

nested if =
IF (
    Data[BL Date] > DATE ( 2018, 8, 31 ),
    "Future",
    IF (
        Data[BL Date] <= DATE ( 2018, 8, 31 )
            && Data[Actual Date] <> 0,
        "Late",
        IF (
            Data[BL Date] <> 0
                && Data[Actual Date] <> 0
                && Data[Variance] <= 0,
            "On-Time",
            "Late"
        )
    )
)

And...

SWITCH FORMULA =
SWITCH (
    TRUE (),
    Data[BL Date] > DATE ( 2018, 8, 31 ), "Future",
    Data[BL Date] <= DATE ( 2018, 8, 31 )
        && Data[Actual Date] <> 0, "Late",
    Data[BL Date] <> 0
        && Data[Actual Date] <> 0
        && Data[Variance] <= 0, "On-Time",
    "Late"
)

 

  • MFelix's avatar
    MFelix
    Super User
    7 years ago
    Hi Sean,

    You are absolutly correct about the AND I was only looking at the final result and prefer the && so never look at the number of arguments, however I prefer the SWITCH instead of the nested if as I show on the second measure.

    Regards
    MFelix
    • Sean's avatar
      Sean
      Community Champion
      7 years ago

      I prefer SWITCH as well as it is easier to read and less opening and closing parentheses to keep track of.

      SWITCH is internally converted into nested IFs anyway :smileyhappy:

      BTW I see you added the <>0 above :smileywink:

      • MFelix's avatar
        MFelix
        Super User
        7 years ago
        Yes thank you for pointing it out.

        Hopefully one of our answers will help.
  • Stuznet's avatar
    Stuznet
    Helper V
    7 years ago

    Thank you so much for your help but the correct formula I'm looking is this. Now the result are matching. 

     

    Spoiler
    SWITCH FORMULA =
    SWITCH (
    TRUE (),
    Data[BL Date] > DATE ( 2018, 8, 31 ), "Future",
    Data[BL Date] <= DATE ( 2018, 8, 31 )
    && ISBLANK(Data[Actual Date]) , "Late",
    Data[BL Date] <= DATE ( 2018, 8, 31 )
    && ISBLANK(Data[Actual Date]) = FALSE()
    && Data[Variance] <= 0, "On-Time",
    "Late"
    )