Forum Discussion
DAX logic based on refresh failures or inprogress
Hi, jakaihammuda
try belowlatest status = var a = MAXX(all('status'[Load Start Timestamp]),'status'[Load Start Timestamp]) VAR b = MINX(FILTER(ALL('status'),'status'[Entity Name]=MAX('status'[Entity Name]) && 'status'[Load Start Timestamp]=a), 'status'[Status Name] ) var c = MAXX(FILTER(ALL('status'),'status'[Status Name]="succeeded" && 'status'[Entity Name]=MAX('status'[Entity Name])), 'status'[Load Start Timestamp]) var d = IF(b<>"succeeded",c) RETURN d
| Load Start Timestamp | Entity Name | Status Name |
| 2023-12-18 21:30:06.1127300 | CIS | In Progress |
| 2023-12-17 21:30:04.8066100 | CIS | Failed |
| 2023-12-16 22:30:03.1503730 | CIS | Succeeded |
Hi, jakaihammuda
try below
latest status =
var a = MAXX(all('status'[Load Start Timestamp]),'status'[Load Start Timestamp])
VAR b = MINX(FILTER(ALL('status'),'status'[Entity Name]=MAX('status'[Entity Name]) && 'status'[Load Start Timestamp]=a),
'status'[Status Name]
)
var c = MAXX(FILTER(ALL('status'),'status'[Status Name]="succeeded" && 'status'[Entity Name]=MAX('status'[Entity Name])),
'status'[Load Start Timestamp])
var d = IF(b<>"succeeded",c)
RETURN
d
Dangar332, Amazing!! You have made my day and taught me a valuable new skill in DAX!
Thank you very very much for taking the time to help me on this 🙂
Wishing you a fantstaic new year!Hi Dangar332
Just have one more point on you resolution that ive found.
All works great in terms of taking the correct refresh timestamp! However there is one thing i noticed. I dont think the correct Status Name is being brought back, primarily when there has been a failure. Example below.
From the data I provided. If 12/18/2023 (lets say this is today) ended up with the status name being "Failed". The logic is correct in then taking the Load Start TimeStamp of 16/12/2023. However, it seems as thought it is also taking the Status Name which is associated with that Load Start TimeStamp, as opposed to keeping the 'todays' actual status name of the report refresh, which should be "Failed".
Any idea what we can add to accomodate this?
Appreciate your timehi, jakaihammuda
as your description you want timestampif status ="failed" same time stamp of ""failed"(12/18/2023) and if status is not faield then time stamp of latest "succeeded"(12/16/2023)
right
ten try belowlatest status = var a = MAXX(all('status'[Load Start Timestamp]),'status'[Load Start Timestamp]) VAR b = MINX(FILTER(ALL('status'),'status'[Entity Name]=MAX('status'[Entity Name]) && 'status'[Load Start Timestamp]=a), 'status'[Status Name] ) var c = MAXX(FILTER(ALL('status'),'status'[Status Name]="succeeded" && 'status'[Entity Name]=MAX('status'[Entity Name])), 'status'[Load Start Timestamp]) var d = IF(b="failed",a,c) RETURN dBoth ways of doing it provide thes same output as shown below
I think what may be an issue is that i was Summarizing on the "Status Name" as LAST. Which provides the below, so even with your updated DAX, it still always shows as Succeeded as opposed to having Failed when it should have, based on the 18/12/2023 being the latest date so it needs ot take whatever the latest status name is, regardless pretty much. because the loadstarttimestamp logic still applies and that works fineSummarizing via LAST was the only way i could think of to get the reports nly showing one by one and not duplicating. If the DAX only displayed the reports one by one i wouldnt need to Summarise, which i didnt think i would anyway because we are telling it what Status Name to take.
Hope that makes sense?
My data table, for reference: