topic Re: Get a list of repeat offenders in DAX Commands and Tips

Get a list of repeat offenders

Anonymous — Wed, 11 Mar 2020 21:58:17 GMT

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!

Re: Get a list of repeat offenders

Greg_Deckler — Thu, 12 Mar 2020 01:25:34 GMT

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.

Re: Get a list of repeat offenders

Anonymous — Thu, 12 Mar 2020 15:56:06 GMT

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