Nested IF formula optimization

[Anonymous]

Maybe it's obvious but somehow I got stuck with this formula. I'm convinced there is a better way to formulate this, but just can't figure it out. To make it worse, it doesn't work correctly

The measure looks at the last modified date of a source file and returns a status.

- if it's more than 7 days (168 hours) old, the file has been retired.

- on a Saturday, if it's more than 48 hours old, it's missing

- on a Sunday, if it's more than 72 hours old, it's missing

- on a working day, if it's more than 24 hours old, it's missing

else

- if it was modified before 10 AM, it's OK

- if it was modified before 12 PM, it's pending

- if it was modified after 12 AM, it's delayed

Ok so my

question #1: Is there a better way of writing a formula that gives the desired results

question #2: Even if it's older than 24 hours on a workday, if it's last modified date is <10 AM or <12 PM, it still returns either OK or pending, instead of missing. What's wrong? Thanks for any help!

backlogHealth =
IF (
    DATEDIFF ( backlogRecord[Date modified], NOW (), HOUR ) > 168,
    "Retired",
    IF (
        WEEKDAY ( NOW () ) = 7,
        IF (
            DATEDIFF ( backlogRecord[Date modified], NOW (), HOUR ) > 48,
            "Missing",
            IF (
                HOUR ( backlogRecord[Date modified] ) < 10,
                "OK",
                IF ( HOUR ( backlogRecord[Date modified] ) < 12, "Pending", "Delayed" )
            )
        ),
        IF (
            WEEKDAY ( NOW () ) = 1,
            IF (
                DATEDIFF ( backlogRecord[Date modified], NOW (), HOUR ) > 72,
                "Missing",
                IF (
                    HOUR ( backlogRecord[Date modified] ) < 10,
                    "OK",
                    IF ( HOUR ( backlogRecord[Date modified] ) < 12, "Pending", "Delayed" )
                )
            ),
            IF (
                DATEDIFF ( backlogRecord[Date modified], NOW (), HOUR ) > 24,
                "Missing",
                IF (
                    HOUR ( backlogRecord[Date modified] ) < 10,
                    "OK",
                    IF ( HOUR ( backlogRecord[Date modified] ) < 12, "Pending", "Delayed" )
                )
            )
        )
    )
)

[Greg_Deckler · Accepted solution]

One thing you could do would be to define this variable:

VAR LastModifiedDuration = backlogRecord[Date modified], NOW (), HOUR )

Also, I would switch to a SWITCH statement.

[Greg_Deckler · Accepted solution]

One thing you could do would be to define this variable:

VAR LastModifiedDuration = backlogRecord[Date modified], NOW (), HOUR )

Also, I would switch to a SWITCH statement.

[WCrayger]

Hi Greg,

Wondering if you can offer input to a similar scenario. I have a measure using nested if's that referene 3 other measures and 3 different fields, so a SWITCH won't work (to my knowledge). Independantly, the measures perform fine, but in the nested if they choke.

Any feedback or suggestions would be great.

Thank you.

IF(
    [Work Order Invoice Total] > 0
    ,[Work Order Invoice Total]
    ,IF(
        [Work Order ISP Charge] > 0
        ,[Work Order ISP Charge] * 1.3
        ,IF(
            [Work Order Client NTE Total] > 0
            ,[Work Order Client NTE Total]
            ,350
        )
    )
)

[Anonymous]

I would use the nested if to calculate a column:

IF(
    [Work Order Invoice Total] > 0
    ,"A"
    ,IF(
        [Work Order ISP Charge] > 0
        ,"B"
        ,IF(
            [Work Order Client NTE Total] > 0
            ,"C"
            ,"D"
        )
    )
)

 Now you can use SWITCH based on A, B, C, D values in the calculated column.

[Anonymous]

Thanks for your prompt reply @Greg_Deckler!

SWITCH will likely do the trick. I was looking for CASE but couldn't find the DAX equivalent. Now I think I got it. Will let you know as soon as I get a chance to test it.

[Anonymous · Accepted solution]

@Greg_Deckleryes it did the trick! Thank you. Here's my new formula for that calculated column:

backlogHealth =
VAR hoursElapsed =
    DATEDIFF ( backlogRecord[Date modified], NOW (), HOUR )
RETURN
    SWITCH (
        TRUE (),
        hoursElapsed > 168, "Retired",
        WEEKDAY ( NOW () ) = 7
            && hoursElapsed > 48, "Missing",
        WEEKDAY ( NOW () ) = 1
            && hoursElapsed > 72, "Missing",
        WEEKDAY ( NOW () ) < 7
            && hoursElapsed > 24, "Missing",
        HOUR ( backlogRecord[Date modified] ) < 10, "OK",
        HOUR ( backlogRecord[Date modified] ) < 12, "Pending",
        "Delayed"
    )