Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GidgetRae
Frequent Visitor

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

Screenshot 2021-08-27 232548.png

 

Thanks in advance for any help you can provide. 

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

View solution in original post

2 REPLIES 2
ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors