Nested IF/AND Statement

[Stuznet]

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!$A$1,X2<>0, Y2<=0),"On-Time","Late")))

 

This is the statement I wrote in power bi but I'm getting syntax error. 

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(Data[BL Date],Data[Actual Date]<>0),Data[Variance] <= 0),"On-Time","Late" )))

Can someone please help? 

Thanks much!

[Sean · Accepted solution]

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!

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]

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 · Accepted solution]

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!

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 · Accepted solution]

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]

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]

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 

BTW I see you added the <>0 above 

[MFelix]

Yes thank you for pointing it out.

Hopefully one of our answers will help.

[Stuznet]

@MFelix @Sean both of you are rock star. I tried both formulas Nested IF and Switch and I think I'll go with Switch function, but when I compared the new calculated column "Switch Formula" against my original data Status 2 I'm not getting the correct result.