Forum Discussion
Nested IF formula optimization
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 :smileyfrustrated:
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" )
)
)
)
)
)
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_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"
)
5 Replies
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.
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.
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"
)
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 ) ) )
