Count streaks or consecutive repeat of values DAX

Question

I have a table that is quite simple:

User	Date	Status
John	May 2021	Success
Doe	May 2021	Fail
John	Aug 2021	Fail
Doe	Aug 2021	Fail
Doe	Sep 2021	Success
Doe	Oct 2021	Success
John	OCt 2021	Fail

I want to count how many times a user fails repeatedly but reset the count when it succeeds.

In the example above I would want to add a column like this :

User	Date	Status	Streak
John	May 2021	Success	0
Doe	May 2021	Fail	0
John	Aug 2021	Fail	0
Doe	Aug 2021	Fail	1
Doe	Sep 2021	Success	0
Doe	Oct 2021	Success	0
John	OCt 2021	Fail	1

Now this streak count would have to increase even if the user did not appear in a month as the example shown. I can not use power query and my main concern is the discrepancy in dates since sometimes users can have a streak since they only got tested months apart. and so on.

Alexis Olson Alexis Olson · Accepted Answer · 2021-10-06T16:33:31

How about this?

Table.AddColumn(
    #"Previous Step",
    "Streak",
    (r) => if r[Status] = "Fail"
           then List.Count(
                    List.LastN(
                        Table.SelectRows(
                            #"Previous Step",
                            each [User] = r[User] and [Date] < r[Date]
                        )[Status],
                        each _ = "Fail"
                    )
                )
           else 0,
    Int64.Type
)

If the [Status] is "Fail", then it will take the table from the #"Previous Step" and Table.SelectRows just the rows where the [User] is the same as in the current row and the [Date] is before the date in the current row and return just the [Status] column from that filtered table. Treating this single column as a list, it then takes the List.LastN occurrences of "Fail" from that list and does a List.Count of how many of those there are.

Count streaks or consecutive repeat of values DAX

2 Answers