Get a list of repeat offenders

[Anonymous]

Looking to get a list of only the Model where Status went from Active to {Inactive, Unknown, anything else} and back to Active.

The status change can happen multiple times in a day.

Following scenario should return only Model 1 as the status went from Active to some other status and came back to Active.

Here's the sample data,

Appreciate your inputs!

[Greg_Deckler]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

Second, I'm not sure I am 100% on the requirements. If things change multiple times during a single day, there is no time component so it would be impossible to tell the "last" or "first" value unless you have something like an Index column.

Third, perhaps something like the following:

Measure =
  VAR __Model = MAX('Table'[Model])
  VAR __FirstDate = MINX('Table',[Date])
  VAR __LastDate = MAXX('Table',[Date])
  VAR __FirstStatus = MINX(FILTER('Table','Table'[Date]=__FirstDate),[Status])
  VAR __LastStatus = MAXX(FILTER('Table','Table'[Date]=__LastDate),[Status])
  VAR __OtherStatus = 
        IF(
          COUNTROWS(
            FILTER(
              ALL('Table'),
              'Table'[Date] >= [FirstDate] && 
                'Table'[Date] <= [LastDate]) &&
            )
          ) > 0,
          1,0
        )
RETURN
  IF(__FirstStatus = "Active" && __LastStatus = "Active" && __OtherStatus = 0,1,0)
  



      

Put that in a visualization along with Model.

[Anonymous]

Hi @Greg Thank you for your inputs. I will keep the guidelines in mind.

Let's ignore the same day changes for the sake of simplicity. 

While looking at the assignment for __OtherStatus, did you mean to add another filter for Status column? The way I understand, it is counting all rows rows between FirstDate and LastDate and assigning 1 or 0 based on that. Shouldn't it count the rows when 'Table'[Status] IN {"Unknown", "Inactive", "Something else"}

Here's the actual table instead of a picture.

Date	Model	Status
20200301	1	Active
20200301	2	Active
20200303	2	Active
20200304	1	Inactive
20200304	2	Active
20200305	1	Unknown
20200305	4	Active
20200305	3	Unknown
20200307	1	Active
20200308	3	Unknown