Forum Discussion

djo_ops's avatar
djo_ops
Regular Visitor
7 years ago
Solved

DAX Expression Help

I'm trying to write a nested if DAX statement where one piece may have one or more logical tests. For whatever reason, the statement won't produce the result in quotations which is the STATUS after it runs through the first two pieces of logic. In this case its "CONS. B/O" AND "OPEN". Any ideas?

 

Status = IF(ORDHDR00_ORDDTL00[Cons. B/O Flag] = "Y", "CONS. B/O",
IF(ORDHDR00_ORDDTL00[Pick Date Dec] = 0, IF(ORDHDR00_ORDDTL00[Released Date Dec] = 0, IF(ORDHDR00_ORDDTL00[Shipped Date Dec] = 0, IF(ORDHDR00_ORDDTL00[B/O Flag] <> "B", IF(ORDHDR00_ORDDTL00[Detail Future Flag] <> "F", IF(ORDHDR00_ORDDTL00[Order Status] = "",
IF(ORDHDR00_ORDDTL00[Held Status] = "N", "OPEN",
IF(ORDHDR00_ORDDTL00[Pick Date Dec] <> 0, IF(ORDHDR00_ORDDTL00[Ack. Pick Date Dec] = 0, IF(ORDHDR00_ORDDTL00[Released Date Dec] = 0, IF(ORDHDR00_ORDDTL00[Shipped Date Dec] = 0, "RLS.PICK",
IF(ORDHDR00_ORDDTL00[Pick Date Dec] <> 0, IF(ORDHDR00_ORDDTL00[Ack. Pick Date Dec] <> 0, IF(ORDHDR00_ORDDTL00[Released Date Dec] = 0, IF(ORDHDR00_ORDDTL00[Shipped Date Dec] = 0, "ACK. PICK", BLANK()))))))))))))))))
Need Help
  • edhans's avatar
    edhans
    7 years ago

    Try using my SWITCH statment.

     

    Or you have to modify your IF statement to only have 4 if statements.

     

    I'm not going to redo the whole thing, but look at the first two parts here:

     

    Status =
IF (
    ORDHDR00_ORDDTL00[Cons. B/O Flag] = "Y",
    "CONS. B/O",
    IF (
        ORDHDR00_ORDDTL00[Pick Date Dec] = 0
            && ORDHDR00_ORDDTL00[Released Date Dec] = 0
            && ORDHDR00_ORDDTL00[Shipped Date Dec] = 0
            && ORDHDR00_ORDDTL00[B/O Flag] <> "B"
            && ORDHDR00_ORDDTL00[Detail Future Flag] <> "F"
            && ORDHDR00_ORDDTL00[Order Status] = ""
            && ORDHDR00_ORDDTL00[Held Status] = "N"
            && "OPEN",
        IF (
            ORDHDR00_ORDDTL00[Pick Date Dec] <> 0,
            IF (
                ORDHDR00_ORDDTL00[Ack. Pick Date Dec] = 0,
                IF (
                    ORDHDR00_ORDDTL00[Released Date Dec] = 0,
                    IF (
                        ORDHDR00_ORDDTL00[Shipped Date Dec] = 0,
                        "RLS.PICK",
                        IF (
                            ORDHDR00_ORDDTL00[Pick Date Dec] <> 0,
                            IF (
                                ORDHDR00_ORDDTL00[Ack. Pick Date Dec] <> 0,
                                IF (
                                    ORDHDR00_ORDDTL00[Released Date Dec] = 0,
                                    IF ( ORDHDR00_ORDDTL00[Shipped Date Dec] = 0, "ACK. PICK", BLANK () )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

    SO you need lots of "ands" which is && in DAX, not lots of IF statements.

7 Replies

  • edhans's avatar
    edhans
    Community Champion
    7 years ago

    Not sure where to begin. Here is your statement formatted. Where is it failing to evaluate?

     

    And as an aside, you might consider rewriting this as a SWITCH() statement rather than a dozen nested IF() statements.

     

    Status =
IF (
    ORDHDR00_ORDDTL00[Cons. B/O Flag] = "Y",
    "CONS. B/O",
    IF (
        ORDHDR00_ORDDTL00[Pick Date Dec] = 0,
        IF (
            ORDHDR00_ORDDTL00[Released Date Dec] = 0,
            IF (
                ORDHDR00_ORDDTL00[Shipped Date Dec] = 0,
                IF (
                    ORDHDR00_ORDDTL00[B/O Flag] <> "B",
                    IF (
                        ORDHDR00_ORDDTL00[Detail Future Flag] <> "F",
                        IF (
                            ORDHDR00_ORDDTL00[Order Status] = "",
                            IF (
                                ORDHDR00_ORDDTL00[Held Status] = "N",
                                "OPEN",
                                IF (
                                    ORDHDR00_ORDDTL00[Pick Date Dec] <> 0,
                                    IF (
                                        ORDHDR00_ORDDTL00[Ack. Pick Date Dec] = 0,
                                        IF (
                                            ORDHDR00_ORDDTL00[Released Date Dec] = 0,
                                            IF (
                                                ORDHDR00_ORDDTL00[Shipped Date Dec] = 0,
                                                "RLS.PICK",
                                                IF (
                                                    ORDHDR00_ORDDTL00[Pick Date Dec] <> 0,
                                                    IF (
                                                        ORDHDR00_ORDDTL00[Ack. Pick Date Dec] <> 0,
                                                        IF (
                                                            ORDHDR00_ORDDTL00[Released Date Dec] = 0,
                                                            IF ( ORDHDR00_ORDDTL00[Shipped Date Dec] = 0, "ACK. PICK", BLANK () )
                                                        )
                                                    )
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)
    • djo_ops's avatar
      djo_ops
      Regular Visitor
      7 years ago

      Aplogies for the formatting.

       

      I'm losing it after:

      "OPEN",

      So the logic that returns after this portion gives me blank values instead of "RLS. PICK" or "ACK. PICK". I haven't dabbled much with a switch statement and was unsuccessful with it in this setting, but would appreciate some guidance on how to implement it with the statement below.

       

      edhans wrote:

      Not sure where to begin. Here is your statement formatted. Where is it failing to evaluate?

       

      And as an aside, you might consider rewriting this as a SWITCH() statement rather than a dozen nested IF() statements.

       

      Status =
IF (
    ORDHDR00_ORDDTL00[Cons. B/O Flag] = "Y",
    "CONS. B/O",
    IF (
        ORDHDR00_ORDDTL00[Pick Date Dec] = 0,
        IF (
            ORDHDR00_ORDDTL00[Released Date Dec] = 0,
            IF (
                ORDHDR00_ORDDTL00[Shipped Date Dec] = 0,
                IF (
                    ORDHDR00_ORDDTL00[B/O Flag] <> "B",
                    IF (
                        ORDHDR00_ORDDTL00[Detail Future Flag] <> "F",
                        IF (
                            ORDHDR00_ORDDTL00[Order Status] = "",
                            IF (
                                ORDHDR00_ORDDTL00[Held Status] = "N",
                                "OPEN",
                                IF (
                                    ORDHDR00_ORDDTL00[Pick Date Dec] <> 0,
                                    IF (
                                        ORDHDR00_ORDDTL00[Ack. Pick Date Dec] = 0,
                                        IF (
                                            ORDHDR00_ORDDTL00[Released Date Dec] = 0,
                                            IF (
                                                ORDHDR00_ORDDTL00[Shipped Date Dec] = 0,
                                                "RLS.PICK",
                                                IF (
                                                    ORDHDR00_ORDDTL00[Pick Date Dec] <> 0,
                                                    IF (
                                                        ORDHDR00_ORDDTL00[Ack. Pick Date Dec] <> 0,
                                                        IF (
                                                            ORDHDR00_ORDDTL00[Released Date Dec] = 0,
                                                            IF ( ORDHDR00_ORDDTL00[Shipped Date Dec] = 0, "ACK. PICK", BLANK () )
                                                        )
                                                    )
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
)

       

      • edhans's avatar
        edhans
        Community Champion
        7 years ago

        I'd need to see the data, because there is nothing inherently wrong with the nested IFs, it just isn't trapping what you want, but without the data, I cannot see why.

         

        As for redoing it with SWITCH, consider this...

         

        Status =
SWITCH (
    TRUE (),
    ORDHDR00_ORDDTL00[Cons. B/O Flag] = "Y", "CONS. B/O",
    ORDHDR00_ORDDTL00[Pick Date Dec] = 0
        && ORDHDR00_ORDDTL00[Released Date Dec] = 0
        && ORDHDR00_ORDDTL00[Shipped Date Dec] = 0
        && ORDHDR00_ORDDTL00[B/O Flag] <> "B"
        && ORDHDR00_ORDDTL00[Detail Future Flag] <> "F"
        && ORDHDR00_ORDDTL00[Order Status] = ""
        && ORDHDR00_ORDDTL00[Held Status] = "N", "OPEN",
    ORDHDR00_ORDDTL00[Pick Date Dec] <> 0
        && ORDHDR00_ORDDTL00[Ack. Pick Date Dec] = 0
        && ORDHDR00_ORDDTL00[Released Date Dec] = 0
        && ORDHDR00_ORDDTL00[Shipped Date Dec] = 0, "RLS.PICK",
    ORDHDR00_ORDDTL00[Pick Date Dec] <> 0
        && ORDHDR00_ORDDTL00[Ack. Pick Date Dec] <> 0
        && ORDHDR00_ORDDTL00[Released Date Dec] = 0
        && ORDHDR00_ORDDTL00[Shipped Date Dec] = 0, "ACK. PICK",
    BLANK ()
)

        Now that I look at it like this, your nested IFs are all required to be TRUE to get "OPEN". If any are false, it doesn't drop to the next IF, it simply goes all the way to the end. Almost none of your IF statments have a false condition. It isn't returning BLANK() from your final IF statement. It is returning BLANK() because an IF statement with no false condition returns BLANK() by default if the logic doesn't return true.

         

        In other words, the IF statement after the "OPEN" result is not triggered unless all of the preceding IFs are true, and the ultimate IF before open is <> "N".

        I am not sure that is what you are intending to do. Maybe the SWITCH() layout above makes it more clear how I think Power BI is processing this.

         

        EDIT: Small clarification. Your IF statements taht are the equivalent to this part of the SWITCH():

            ORDHDR00_ORDDTL00[Pick Date Dec] = 0
        && ORDHDR00_ORDDTL00[Released Date Dec] = 0
        && ORDHDR00_ORDDTL00[Shipped Date Dec] = 0
        && ORDHDR00_ORDDTL00[B/O Flag] <> "B"
        && ORDHDR00_ORDDTL00[Detail Future Flag] <> "F"
        && ORDHDR00_ORDDTL00[Order Status] = ""
        && ORDHDR00_ORDDTL00[Held Status] = "N", "OPEN",

        If those all are true, but the [Held Status] <> "N", then it will go to the next IF. If any in the IF method before the [HELD STATUS] are false, it drops to the end and returns the default BLANK(). You could redo the IFs with the && (and) connectors, but I still like the SWITCH method. Easier to read IMHO. :smileyhappy:

         

        In the SWITCH() method. they all have to be true. If any are false - any - then it goes to the next section.