DAX Expression Help

[djo_ops]

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

[edhans]

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]

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]

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. 

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

[djo_ops]

This helps alot.

Let me break this out a bit more. So, there are 4 separate pieces of logic here that i'm compounding and the logic preceding the returned result is in quotations all have to be true, otherwise it will return the next result and so on. The ultimate false condition will be BLANK() if any of the preceding pieces of logic aren't true. It's basically an IF/ELSE statement, but no such statement can be made in DAX. Does this help?

Status =
--First piece 
IF (
    ORDHDR00_ORDDTL00[Cons. B/O Flag] = "Y",
    "CONS. B/O",
   
--Second Piece
 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",

--Third Piece                                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",

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

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.

[djo_ops]

@edhansThank you for help. This is working perfectly.

[edhans]

Great @djo_ops . Glad we got it figured out. By the way, one of the things that really helped me was seeing the DAX formatted. It brings things out you might not otherwise see. After building your formulas, paste them to DAXFORMATTER. It will format them for you! I keep one tab in my browser on that page all day long.