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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Counting consecutive the rows in a category

 
Hello everyone, I'm trying to create a calculated column where I count the numer of consecutive weeks that an employee is in a determined quartile. It should be a DAX calculated column. To take into account, an different employees may start working at different id_weeks.

To add on, everytime that an employee changes from quartile, the counter is reseted.

As an example, I have to be able to create the column "Weeks on quartile" in the following table.

 

id_weekid_employeequartileWeeks on quartile
11q11
21q12
31q21
41q11
51q12
32q31
42q41
52q42
23q21
33q22
43q23
53q24
63q11
73q12
1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
Community Champion

@Anonymous This is Cthulhu: Cthulhu - Microsoft Power BI Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Community Champion
Community Champion

@Anonymous This is Cthulhu: Cthulhu - Microsoft Power BI Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi, I had to make some changes but got it working.
Here is the final result:

Weeks in Quartile =
VAR __index = CALCULATE(MAX([id_week])) //What is my current row index?
VAR __group = CALCULATE(MAX([id_employee])) //What is my current group?
VAR __tmpTable1 = FILTER(ALL('Table'),[id_employee]=__group&&[id_week]<__index) //Return all rows earlier than the current row within the same "group"
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[id_week] - MAXX(FILTER(ALL('Table'),[id_week]<EARLIER([id_week]) && [id_employee]=EARLIER([id_employee])),[id_week])) //For each returned row, calculate the difference between the current index value and the previous index value within the same group. For rows in grouped sequence, this will be 1 but for rows within a group that are out-of-sequence this value will be greater than 1
VAR __max = MAXX(__tmpTable2,[id_week]) //Figure out the max index in the current filtered table.
VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[id_week]) //In order to account for "skips" in the grouping, figure out the max index value of the latest "skip" (the row right after the skip where the group starts again) This will be the greatest index where the difference from the previous index in the same group is greater than 1 (previous row)
VAR __tmpTable3 = FILTER(__tmpTable2,[id_week]>=__maxStart) //Filter out all the other junk because we don't want to count rows before the skip
RETURN IF(ISBLANK(__max),1,IF(__max=__index-1,COUNTROWS(__tmpTable3)+1,1)) //If __max is blank, we know that we are at the start of the table, so 1. If the max index of our original table is 1 less than the current index, we know that we are in sequence so we count all of our filtered rows (which don't include rows past a "skip"), otherwise return 1 because we know we are on the row immediately after a "skip.


Thanks!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors