Forum Discussion
Checking status for three most recent dates per device
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!
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")
3 Replies
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")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 🙂
