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
hi, jakaihammuda
as your description you want timestamp
if 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 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="failed",a,c)
RETURN
d
Both 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 fine
Summarizing 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:
hi, jakaihammuda
try below measureMeasure 3 = var a = CALCULATE(max('status'[Load Start Timestamp]),ALLEXCEPT('status','status'[Entity Name])) var b = CALCULATE(MIN('status'[Status Name]),'status'[Load Start Timestamp]=a,'status'[Entity Name]=MAX('status'[Entity Name])) var c = MAXX( FILTER( 'status',[Entity Name]=MAX([Entity Name]) && [Status Name]="succeeded"), [Load Start Timestamp]) RETURN IF(b="failed",a,c)for latest status
latest status = CALCULATE(MIN('status'[Status Name]),FILTER('status','status'[Load Start Timestamp]=[Measure 3]))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.and don't forgot to give kudos.
hi, jakaihammuda
download file LINKThis still takes the load timestamp of the one that failed when it should be last successful..
if the 18th failed (today/latest status) then that should appear as the acualy status
but for the load timestamp (which i use to show how up to date the data is) i want to use whatever the last successful load start timestamp was. whilst still having the status as Failed - because it did fail the load time stamp (data) didnt updateIt could be as simple as renaming "Succeeded" to be Failed based on IF (b = "Failed", A). So then in A there could be a rename, replace or the status "Suceeded" to appear as "Failed"
??hi, jakaihammuda
Measure 3 = var a = CALCULATE(max('status'[Load Start Timestamp]),ALLEXCEPT('status','status'[Entity Name])) var b = CALCULATE(MIN('status'[Status Name]),KEEPFILTERS('status'[Load Start Timestamp]=a)) var c = MAXX( FILTER( 'status',[Entity Name]=MAX([Entity Name]) && [Status Name]="succeeded"), [Load Start Timestamp]) RETURN SWITCH(TRUE(), b="failed",c,c)Doesnt seem to work.
no worries ill see if theres anything else i can do. Thank you nontheless.Im unable to do so