Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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" )
)
)
)
)
)
Solved! Go to 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_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"
)
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.
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 ) ) )
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.
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"
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |