Forum Discussion

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 i...

Need Help

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.

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

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

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.

djo_ops

Regular Visitor

7 years ago

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

Community Champion

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.

Forum Discussion

DAX Expression Help

Related content

Weighed Average DAX Expression

DAX expression. HELP

dax expression help needed

Dax Expression

Help!!! Expression Error