Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am looking to create three difeerent columns with flags based on the below conditions. The trick comes when the flag should show on rows, even though the value os not there. For example (ID: 19 & 53 below should show "Job Complete" on all rows since the status of '10' is now active.)
If an ID has ever received Status Code 10 then a “Job Completed” flag is applied to all instances of the ID (even on rows that don’t fulfill this requirement), else Not Complete
If an ID has received Status Codes 30, 60, or 280 and last modified time is within 48 hours of current time then a “Touched” flag is applied to all instances of the dispatch ID (even on rows that don’t fulfill this requirement), else Needs Attention
If an ID has NOT received Status Code 10 and 5 hours have passed since most recent last modified timestamp then a "still open - Exceeds guidelines" flag is applied to all instances of the dispatch ID (even on rows that don’t fulfill this requirement); otherwise "Still Open - Within Guidelines"
Here are how the tables look:
ID | STATUS_CODE | LAST_MODIFIED DATE |
19 | 30 | 1/20/2020 |
53
| 30 | 1/21/2020 |
93 | 60 | 1/20/2020 |
19 | 10 | 1/28/2020 |
50 | 280 | 1/1/2020 |
53 | 10 | 1/27/2020 |
ID | Dispatch Timestamp | Trade Code | Trade_Day Flag |
53 | 2/14/2020 | ASW | 16 |
50 | 1/26/2020 | WQA | 5 |
19 | 12/7/2019 | YTR | 4 |
93 | 11/30/2019 | OIP | 8 |
Hi @Anonymous
Your sample data appears to be incomplete (lacking some scenarios as per description of what you are trying to achieve). It also doesn't have timestamps for computing the number of hours lapsed and the basis of your "current time" like refresh time, etc. So I made do of what is available. Try this:
Status =
VAR __10 =
CALCULATE (
COUNTA ( 'Table'[ID] ),
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[STATUS_CODE] = 10
)
VAR __3060280 =
CALCULATE (
COUNTA ( 'Table'[ID] ),
FILTER (
ALL ( 'Table' ),
'Table'[ID] = EARLIER ( 'Table'[ID] )
&& ( 'Table'[STATUS_CODE] = 30
|| 'Table'[STATUS_CODE] = 60
|| 'Table'[STATUS_CODE] = 280 )
)
)
VAR __LATEST_MODIFIED =
CALCULATE (
MAX ( 'Table'[LAST_MODIFIED DATE] ),
ALLEXCEPT ( 'Table', 'Table'[ID] )
)
VAR __TIME_LAPSED =
DIVIDE ( DATEDIFF ( __LATEST_MODIFIED, NOW (), MINUTE ), 60 )
RETURN
SWITCH (
TRUE (),
__10 >= 1, "Job Completed",
__3060280 >= 1
&& __TIME_LAPSED <= 48, "Touched",
__3060280 >= 1
&& __TIME_LAPSED > 48, "Needs Attention",
ISBLANK ( __10 )
&& __TIME_LAPSED >= 5, "Still open - Exceeds Guidelines",
"Still Open - Within Guidelines"
)
Proud to be a Super User!
Hi @danextian
Thank you for your reply! I updated the description to include the data table examples.
I ended up created separate columns for each flag. I'm having problems creating flags for these two conditions:
If status code is null when [now - dispatch timestamp] >= 24 hours “Unscheduled”
If a dispatch ID has NOT received Status Code 10 and "x" hours have passed since most recent last status timestamp then a "Still open - Exceeds guidelines" flag is applied to all instances of the dispatch ID (even on rows that don’t fulfill this requirement); otherwise "Still Open - Within Guidelines"
The "X" value changes based on conditional column "Trade_Day Flag"
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |