DAX calculated column to get running sum which resets on particular conditions

Hi again mahenkj2 

I've attached a PBIX illustrating a possible Power Query method using List.Generate, which I'm hoping refreshes in an acceptable time.

This is the code for the query called Counter that generates the counter values. This query relies on a table called YourTableSource with columns Index, clean trigger, machine, and type:

let
    // Select required columns from source table
    // Table.Buffer used but may not be necessary
    SourceTable = Table.Buffer(YourTableSource[[Index],[clean trigger],[machine],[type]]),
    RowCount = Table.RowCount(SourceTable),
    GenerateCount =
      List.Generate(
        // Function with no arguments to generate first counter record
        // Record contains [Index, machine, counter]
        ()=>let CurrentRow = SourceTable{[Index= 1]}, CurrentType = CurrentRow[type], CurrentMachine = CurrentRow[machine], CurrentCounter = if CurrentType = 1 then 1 else 0
            in [Index = 1, machine = CurrentMachine, counter = CurrentCounter],
        // Condition to continue loop
        each [Index] <= RowCount,
        // Function to produce next counter record
        each let CurrentRow = SourceTable{[Index = [Index]+1]},
         CurrentType = CurrentRow[type], CurrentMachine = CurrentRow[machine], CurrentCleanTrigger = CurrentRow[clean trigger],
         CurrentCounter = (if CurrentMachine <> [machine] or CurrentCleanTrigger = 1 then 0 else [counter]) + CurrentType
         in [Index = [Index]+1, machine = CurrentMachine, counter = CurrentCounter]    
    ),
    // Convert into table to be joined with main table
    #"Converted to Table" = Table.FromList(GenerateCount, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Index", "counter"}, {"Index", "counter"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Index", Int64.Type}, {"counter", Int64.Type}})
in
    #"Changed Type"

The output counter values appear correct but please test with your actual data.

The important parts to check are the 1st and 3rd arguments of List.Generate.

The final table is called YourTable, which joins YourTableSource and Counter.

On the original DAX version, the poor performance is essentially due to the quadratic relationship between number of sets to be included in the calculation and the number of rows, which eats up RAM. We could possibly rewrite, but I still think Power Query performance would be better.

Let me know how this goes!

Regards,

Owen