Running Count Based on Multiple Fields

[GidgetRae]

I've been 3+ weeks now searching the internet, youtube, this forum, etc. trying to figure out this issue and need help.  In Power Query, or as a calculated column in Power BI, I need to create a column that counts consecutive shifts worked that are over 36 hours.  Below is an example of the data I am working with.  The data set is VERY large so this is just a snippet for testing purposes.  I've included an example column showing what I'm trying to achieve.  Employee ID and Time Between Shifts <36 Hr are my key columns for this but could I included the other columns in case they are useful in any way. 

I'm trying to count (or running total) the consecutive 1s in the "Time Between Shifts <36 Hr" column for each employee. When there is a null in that column the count needs to start over at 1 for the next column that contains a 1 and if the Employee ID changes the count needs to reset to 1.  Hopefully that makes sense.

Thanks in advance for any help you can provide. 

[ImkeF]

Hi @GidgetRae ,
you have to create a nested index, but with the GroupKind.Local-parameter on it (assuming that the data is properly sorted):

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkOlWB0ILwmFlwzEcE4KilQiHl4SsrZkuJQRiiFGKNoweXBDjIgwBN2MWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmployeeID = _t, #"One of many columns" = _t, #"Time between shifts" = _t]),
#"Grouped Rows" = Table.Group(Source, {"EmployeeID", "Time between shifts"}, {{"All", each Table.AddIndexColumn(_, "Example", 1,1)}}, GroupKind.Local),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"One of many columns", "Example"}, {"One of many columns", "Example"})
in
#"Expanded All"

Nested index: (1) NestedIndex in PowerBI - YouTube
GroupKind.Local: Chris Webb's BI Blog: Aggregating By Local Groups In Power Query Chris Webb's BI Blog (crossjoin.co....

!! Next time, please provide sample data in a usable form so that folks who answer questions here don't have to create that manually: 

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

[GidgetRae · Accepted solution]

@ImkeF Thank you for the help. I wasnt' able to get the grouping to work from your suggestion but ended up going with the below dax after creating another index column and merging my table with itself to bring in the prev employee ID & prev time between shifts <36 hr.  Here is the dax I used in case anyone runs into a similar problem.

Consecutive Breaks <36 Hr = 
VAR _RowIndex = 'Time Detail'[Index2]

VAR _EarlierReset = 
    CALCULATE( MAX ( 'Time Detail'[Index2] ), ALL('Time Detail'),'Time Detail'[Index2]<= _RowIndex,'Time Detail'[Count of Consecutive Breaks <36 Hr]=1)

VAR _FirstTableIndex = 
    CALCULATE(MIN('Time Detail'[Index2]),ALL('Time Detail'))

VAR _StartIndex = 
    IF ( _EarlierReset=0, _FirstTableIndex, _EarlierReset )

RETURN 
    
    IF ('Time Detail'[Count of Consecutive Breaks <36 Hr] = 0, BLANK(),
    IF (('Time Detail'[Employee ID]='Time Detail'[Prev Employee ID] || 'Time Detail'[Prev Employee ID]=BLANK()) && 'Time Detail'[Count of Consecutive Breaks <36 Hr] = 1, 1,
    CALCULATE ( 
        SUM ( 'Time Detail'[Time Between Shifts <36 Hr] ), 
        ALL ( 'Time Detail' ), 
        'Time Detail'[Index2] >= _StartIndex && 'Time Detail'[Index2] <= _RowIndex)
    )
    )