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!

Need Help

ImkeF

3 years ago

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

3 years ago

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.

11 Replies

Shaurya
Memorable Member
3 years ago
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
  Not applicable
  3 years ago
  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
    Memorable Member
    3 years ago
    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?

ImkeF

Community Champion

3 years ago

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
Not applicable
3 years ago
Thanks for your time, that works perfectly!

ImkeF
Community Champion
3 years ago
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
  Not applicable
  3 years ago
  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!

ImkeF

Community Champion

3 years ago

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

Forum Discussion

Counting the rows in a category

11 Replies

Related content

Count rows per category

Counting rows with multiple categories per row.

Unique Count in Row convert

Count rows with condition

Measure to count the # rows in each category