Running count that resets if a row has a particular value

[danextian]

Hello,

I need your help. I've been trying to do this with DAX but no luck. I'm stuck. What I want to achieve is for the running count to reset if the value of column "Instance within 5 weeks" is 1. If the column value is 1, then the running count resets to 1. If the next row is not 1, then the running count becomes 2 and then 3 and 4 which will reset to 1 if "Instance within 5 weeks" is 1 again.

Here's the pq script of the sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdI7C4MwFAXgvyKZHXLNQzN26Gpfo0iR0qEUdPH/Y87NbSHTFZSA+W6OB6fJ+N605nq+Py7jc9ya5bV/trXZl+97zS9uJ5svyivyePzvuc10UGlKeeWwr6tp0ihZC8peeMc0WJ0iqwfzQl2hpNMABRaE+kI7nUbsBosSOhTqVEqYzun6qqbgdcoHwQ91TUGn/Jnwv5alpqhTTOd0ZKVm6Un9nYgwnuMRSc+5qPkA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, #"Person & Error" = _t, #"QA#" = _t, #"Work week" = _t, #"Running Instance" = _t, #"Instance within 5 weeks" = _t, #"Desired Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Person & Error", type text}, {"QA#", type text}, {"Work week", Int64.Type}, {"Running Instance", Int64.Type}, {"Instance within 5 weeks", Int64.Type}, {"Desired Result", Int64.Type}})
in
    #"Changed Type"

[OwenAuger · Accepted solution]

Hello,

For a DAX calculated column I would do something like this (it assumes the Index column increments by 1 on each row):

Result =
VAR CurrentIndex = Data[Index]
VAR PreviousResetIndex =
    CALCULATE (
        MAX ( Data[Index] ),
        ALL ( Data ),
        Data[Index] <= CurrentIndex,
        Data[Instance within 5 weeks] = 1
    )
RETURN
    CurrentIndex - PreviousResetIndex
        + 1

Does that work?

[danextian]

@OwenAuger

Thank you!  This works like a charm. I was actually able to  come up with the same result but a longer solution. Had to use LASTNONBLANK() to do an filldown of the running instance only if the instance column is 1 and then create another column to do a count based on the unique criteria and if index is less than the current index.

Your solution is way shorter and more elegant.