Forum Discussion

GidgetRae's avatar
GidgetRae
Frequent Visitor
5 years ago
Solved

Running Count Based on Multiple Fields

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 colu...
Need Help
  • GidgetRae's avatar
    GidgetRae
    5 years ago

    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)
    )
    )
ImkeF's avatar
ImkeF
Community Champion
5 years ago

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.uk)

 

!! 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-p/963216 

  • GidgetRae's avatar
    GidgetRae
    Frequent Visitor
    5 years ago

    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)
    )
    )