Count streaks or consecutive repeat of values DAX

user_10733613 0
Viewed 911

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.

dax powerbi
2 Answers

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.

user_7652260

can you add conditional column ?

if status = success new_column = 0 , like this.

enter image description here

enter image description here

user_119923740
Related