How can I filter out certain groups of records based on the most recent record's status?

[jarwest]

Hey all,

I've got a bit of a complicated problem I've run into here. To preface, I am getting this data from another report's semantic model that I am not allowed to alter, so I can't use Power Query, I admittedly can't even see a way to edit the query on my end or access Power Query.

I have a list of Reports, when they refreshed, and if the refresh was successful or failed.

My data currently looks something similar to the following. 

Report Name	Refresh Start Time	Status
ReportA	4/10/2026 11:00:00 AM	Succeeded
ReportA	4/10/2026 10:00:00 AM	Failed
ReportA	4/10/2026 9:00:00 AM	Failed
ReportB	4/10/2026 11:00:00 AM	Failed
ReportB	4/10/2026 10:00:00 AM	Succeeded
ReportC	4/10/2026 11:00:00 AM	Succeeded
ReportD	4/10/2026 11:00:00 AM	Failed

I want to show the reports that have failed and have not since successfully refreshed in my data. I currently am just filtering to all failed refreshes, and my table looks like something below

Report Name	Refresh Start Time	Status
ReportA	4/10/2026 10:00:00 AM	Failed
ReportA	4/10/2026 9:00:00 AM	Failed
ReportB	4/10/2026 11:00:00 AM	Failed
ReportD	4/10/2026 11:00:00 AM	Failed

However, I need my table to look something like the table below. I basically want to filter out any records where the report has succeeded it's refresh after a failure, or filtering out any records where the report has been refreshed and fixed already.

Report Name	Refresh Start Time	Status
ReportB	4/10/2026 11:00:00 AM	Failed
ReportD	4/10/2026 11:00:00 AM	Failed

Effectively, I need to know if there's a way to make a custom column with DAX that I can use as a filter that would check the latest record for a given report, and let me filter out any reports based on if the latest record for that report has a Status of Succeeded.

Any and all help is appreciated. 

[danextian]

Why is D included in your expected result when it hasn't succeeded?

[jarwest]

I'm looking for all reports that have failed and have NOT succeeded. I'm basically trying to recreate the Monitor tab's results. I want all reports that have not succeeded.

[Ashish_Mathur · Accepted solution]

Hi,

Write this measure

Last refresh status = CALCULATE(MAX(Data[Status]),FILTER(VALUES(Data[Refresh Start Time]),[Refresh Start Time]=MAX(Data[Refresh Start Time])))

In the filter pane, filter this measure on Failed.

Hope this helps.

[ThxAlot]

Simple enough,

[cengizhanarslan]

Please try the measure below:

Report Has Active Failure =
VAR _ThisReport =
    MAX ( Refreshes[Report Name] )
VAR _LatestTime =
    CALCULATE (
        MAX ( Refreshes[Refresh Start Time] ),
        FILTER ( ALL ( Refreshes ), Refreshes[Report Name] = _ThisReport )
    )
VAR _LatestStatus =
    CALCULATE (
        MAX ( Refreshes[Status] ),
        FILTER (
            ALL ( Refreshes ),
            Refreshes[Report Name] = _ThisReport
                && Refreshes[Refresh Start Time] = _LatestTime
        )
    )
RETURN
    IF ( _LatestStatus = "Failed", 1, BLANK () )

Then apply it as a visual filter:

Filters on this visual → drag Report Has Active Failure → set to is 1