Checking status for three most recent dates per device

[Anonymous]

Hi all,

I am trying to create an indicator that will tell me if there's an issue with a device. "Issue" is defined as the status = failed for all three of the most recent entries per device.

My data looks something like this:

Device	Date	Status
A	8/1/2020	Pass
A	8/4/2020	Fail
A	8/6/2020	Pass
A	8/7/2020	Fail
B	8/10/2020	Pass
B	8/11/2020	Fail
B	8/14/2020	Fail
C	4/1/2020	Pass
C	5/1/2020	Fail
C	6/1/2020	Fail
C	7/1/2020	Fail
D	8/3/2020	Fail
D	8/7/2020	Fail
D	8/15/2020	Fail

And I need an indicator per device (column or measure, either way) that will display the following:

Device	Issue?
A	FALSE
B	FALSE
C	TRUE
D	TRUE

I need to look at each device, find the three most recent dates for that device, check the statuses on those dates, and if all three statuses are fail, indicate that there's an issue. The three most recent dates may be different for different devices.

Any ideas? I'm completely stumped right now and would appreciate any help!

[Ashish_Mathur]

Hi,

Here's a solution using measures only.  You may download my PBI file from here.

Hope this helps.

[Greg_Deckler · Accepted solution]

@Anonymous  - I think you could do it by creating a custom column like:

Rank = RANKX(FILTER('Table (2)', [Device] = EARLIER([Device])),[Date])

Then you could create a measure like:

Measure = 
    VAR __Table = SUMMARIZE(FILTER('Table (2)',[Rank]<=3),[Status])
RETURN
    IF(MINX(__Table,[Status])="Fail" && COUNTROWS(__Table)=1,"TRUE","FALSE")

[Anonymous]

Thank you, @Greg_Deckler! I knew there had to be a way to do this, I just couldn't figure it out.

P.S. I've been benefitting from the solutions you've provided here for the past few years, so thanks for all your indirect help too 🙂