Counting the rows in a category

[Anonymous]

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!

[ImkeF · Accepted solution]

Hi @Anonymous ,
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

[ImkeF · Accepted solution]

Hello @Anonymous ,
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.

[Anonymous]

Thanks for your time, that works perfectly!

[ImkeF · Accepted solution]

Hi @Anonymous ,
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

[Shaurya]

Hi @Anonymous,

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!

[Anonymous]

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!

[Shaurya]

Hi @Anonymous,

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

[Anonymous]

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!

[v-yiruan-msft]

Hi  @Anonymous ,

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.

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​

Best Regards

[Anonymous]

Hi. It isnt't working fine:

Thanks

[ImkeF]

Hi @Anonymous ,
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
)

[Anonymous]

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!