DAX logic based on refresh failures or inprogress

[jakaihammuda]

Hello,

I have a basic 3 column table. However the logic i want to implement i cant quite figure out.

Essentially all i want to do is the following:
If the [statusname] of a report is either Failed or InProgress for the latest loadstarttimestamp, then i want to take the most recent loadstarttimestamp where the [statusname] was Successful, else just keep the normal latest loadstarttimestamp if the report was successful on the latest load.

I have renamed the columns but here are their true namings:
Report = 'Table 1'[EntityName]
Refreshed = 'Table 1'[StatusName]
Data up To = 'Table 1'[LoadStartTimestamp] - this takes the "latest" so will change whenevr another report refresh occurs,

[lbendlin]

Sounds straightforward. What have you tried and where are you stuck?

[jakaihammuda]

Anyting ive done, displays no data so im kind of stuck from the get go. Ive tried Variables, creating a measure then referencing it in a CC. but ive just had no luck with everything so far

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[jakaihammuda]

I hope the above is sufficient enough, it should be as it covers all scenarios. Ive provided an example just for one report name, CIS

With 18th being in progress, itll need to use the last successful load start timestmp until the new status of the 18th is confirmed.

If for example the 18th ended up failing, the same as the 17th, it will then again need to use the last successul laod start timestamp.

[jakaihammuda]

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

[Dangar332 · Accepted solution]

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

[jakaihammuda]

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 time

[Dangar332]

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

[jakaihammuda]

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:

[Dangar332]

hi, @jakaihammuda 

try below measure

Measure 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.

[jakaihammuda]

It 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"

??

[jakaihammuda]

This 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 update

[Dangar332]

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)

[jakaihammuda]

Doesnt seem to work.

no worries ill see if theres anything else i can do. Thank you nontheless.

[Dangar332]

hi, @jakaihammuda 

downlaod file LINK 

[jakaihammuda]

Hi @Dangar332 ,

I got this error when trying to use the "Lates Status" Measure

[Dangar332]

hi, @jakaihammuda 

download file LINK 

[jakaihammuda]

Im unable to do so

[jakaihammuda]

@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!