Forum Discussion

jakaihammuda's avatar
jakaihammuda
Helper III
2 years ago
Solved

DAX logic based on refresh failures or inprogress

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 F...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Dangar332's avatar
    Dangar332
    2 years ago

    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

     

     

     

lbendlin's avatar
lbendlin
Super User
2 years ago

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-Forum/ba-p/963216

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/1447523

jakaihammuda's avatar
jakaihammuda
Helper III
2 years ago
Load Start TimestampEntity NameStatus Name
2023-12-18 21:30:06.1127300CISIn Progress
2023-12-17 21:30:04.8066100CISFailed
2023-12-16 22:30:03.1503730CISSucceeded
  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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's avatar
      jakaihammuda
      Helper III
      2 years ago

      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!

    • jakaihammuda's avatar
      jakaihammuda
      Helper III
      2 years ago

      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's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        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