Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.   ...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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")
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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 🙂