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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Counting 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_week id_employee quartile Weeks on quartile 1 1 q1 1 2 1 q1 2 3 1 q2 1 4 1 q1 1 3 2 q3 1 4 2 q4 1 5 2 q4 2 2 3 q2 1 3 3 q2 2 4 3 q2 3 5 3 q2 4 6 3 q1 5

Thanks in advance!

2 ACCEPTED SOLUTIONS
Super User

Hi @aguuzdo ,
you can modify the measure like so:

``````WeeksInQuartile =
VAR __previousWeek = 'Table'[id_week] - 1
VAR __Result =
RANKX (
FILTER(
'Table',
'Table'[id_employee] = EARLIER('Table'[id_employee])
&& 'Table'[quartile] = EARLIER('Table'[quartile])
&& 'Table'[quartile] = CALCULATE(MAX('Table'[quartile]), FILTER(all('Table'), 'Table'[id_week] = __previousWeek))
),
'Table'[id_week],
'Table'[id_week],
ASC
)
Return
__Result``````

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!

Super User

Hello @aguuzdo ,
thanks for taking the time to point out the error in the previous formula.
Please try out this new approach:

``````WeeksInQuartile =
VAR __StartRangeWeek =
MAXX (
FILTER (
'Table',
'Table'[id_employee] = EARLIER ( 'Table'[id_employee] )
&& 'Table'[id_week] <= EARLIER ( 'Table'[id_week] )
&& 'Table'[quartile] <> EARLIER ( 'Table'[quartile] )
),
'Table'[id_week]
)
VAR __EndRangeWeek =
MINX (
FILTER (
'Table',
'Table'[id_employee] = EARLIER ( 'Table'[id_employee] )
&& 'Table'[id_week] >= EARLIER ( 'Table'[id_week] )
&& 'Table'[quartile] <> EARLIER ( 'Table'[quartile] )
),
'Table'[id_week]
)
VAR __Result =
RANKX (
FILTER (
'Table',
'Table'[id_employee] = EARLIER ( 'Table'[id_employee] )
&& 'Table'[quartile] = EARLIER ( 'Table'[quartile] )
&& 'Table'[id_week] > __StartRangeWeek
&& 'Table'[id_week]
< IF (
__EndRangeWeek = BLANK (),
EARLIER ( 'Table'[id_week] ) + 1,
__EndRangeWeek
)
),
'Table'[id_week],
'Table'[id_week],
ASC
)
RETURN
__Result``````

Performance will probably be terrible on large datasets.
A Power Query solution would probably be much faster in this case.

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!

11 REPLIES 11
Super User

Hello @aguuzdo ,
thanks for taking the time to point out the error in the previous formula.
Please try out this new approach:

``````WeeksInQuartile =
VAR __StartRangeWeek =
MAXX (
FILTER (
'Table',
'Table'[id_employee] = EARLIER ( 'Table'[id_employee] )
&& 'Table'[id_week] <= EARLIER ( 'Table'[id_week] )
&& 'Table'[quartile] <> EARLIER ( 'Table'[quartile] )
),
'Table'[id_week]
)
VAR __EndRangeWeek =
MINX (
FILTER (
'Table',
'Table'[id_employee] = EARLIER ( 'Table'[id_employee] )
&& 'Table'[id_week] >= EARLIER ( 'Table'[id_week] )
&& 'Table'[quartile] <> EARLIER ( 'Table'[quartile] )
),
'Table'[id_week]
)
VAR __Result =
RANKX (
FILTER (
'Table',
'Table'[id_employee] = EARLIER ( 'Table'[id_employee] )
&& 'Table'[quartile] = EARLIER ( 'Table'[quartile] )
&& 'Table'[id_week] > __StartRangeWeek
&& 'Table'[id_week]
< IF (
__EndRangeWeek = BLANK (),
EARLIER ( 'Table'[id_week] ) + 1,
__EndRangeWeek
)
),
'Table'[id_week],
'Table'[id_week],
ASC
)
RETURN
__Result``````

Performance will probably be terrible on large datasets.
A Power Query solution would probably be much faster in this case.

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!

Helper I

Thanks for your time, that works perfectly!

Super User

Hi @aguuzdo ,
you can modify the measure like so:

``````WeeksInQuartile =
VAR __previousWeek = 'Table'[id_week] - 1
VAR __Result =
RANKX (
FILTER(
'Table',
'Table'[id_employee] = EARLIER('Table'[id_employee])
&& 'Table'[quartile] = EARLIER('Table'[quartile])
&& 'Table'[quartile] = CALCULATE(MAX('Table'[quartile]), FILTER(all('Table'), 'Table'[id_week] = __previousWeek))
),
'Table'[id_week],
'Table'[id_week],
ASC
)
Return
__Result``````

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!

Memorable Member

Hi @aguuzdo,

You can use the following formula:

``Weeks on Quartile = RANKX(FILTER('Table','Table'[id_employee]=EARLIER('Table'[id_employee]) && 'Table'[quartile]=EARLIER('Table'[quartile])),'Table'[id_week],,ASC,DENSE)``

Result:

Works for you? Mark this post as a solution if it does!

Helper I

Hi! It works partially, because I need the counter to be reseted everytime a driver changes from quartile, for exaple:

 id_week id_employee quartile Weeks on quartile 1 1 q1 1 2 1 q1 2 3 1 q2 1 4 1 q1 1

Thanks!

Memorable Member

Hi @aguuzdo,

I can see that the numbers in my previous reply match with those in your post. Can you please elaborate this driver change scenario?

Helper I

Sorry, my sample data wasn't taking into consideration every case. Using the code of your reply, if a same id_employee is in quartile q1 then in quartile q2 and then again in quartile q1 I get the following result for the calculated column:
Weeks in quartile
1
1
2

What I should get is:

Weeks in quartile
1
1
1

Because the counter should reset if the weeks in a same quartile aren't consecutive.

Thanks!

Community Support

Hi  @aguuzdo ,

Please check the last reply from  @ImkeF , that can achieve your requirement. Could you please mark her post as Answered if it is OK? It will help the others in the community find the solution easily if they face the same problem as yours.  Thank you.

``````WeeksInQuartile =
VAR __previousWeek = 'Table'[id_week] - 1
VAR __Result =
RANKX (
FILTER(
'Table',
'Table'[id_employee] = EARLIER('Table'[id_employee])
&& 'Table'[quartile] = EARLIER('Table'[quartile])
&& 'Table'[quartile] = CALCULATE(MAX('Table'[quartile]), FILTER(all('Table'), 'Table'[id_week] = __previousWeek))
),
'Table'[id_week],
'Table'[id_week],
ASC
)
Return
__Result​``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

Hi. It isnt't working fine:

Thanks

Super User

Hi @aguuzdo ,
You could use this DAX for that column:

``````WeeksInQuartile =
RANKX (
FILTER (
'Table',
'Table'[id_employee] = EARLIER ( 'Table'[id_employee] )
&& 'Table'[quartile] = EARLIER ( 'Table'[quartile] )
),
'Table'[id_week],
'Table'[id_week],
ASC
)``````

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!

Helper I

Hi! It works partially, because I need the counter to be reseted everytime a driver changes from quartile, for exaple:

 id_week id_employee quartile Weeks on quartile 1 1 q1 1 2 1 q1 2 3 1 q2 1 4 1 q1 1

Thanks!

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors