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!$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!

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"
    )

     

7 Replies

  • 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