Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • ImkeF's avatar
    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's avatar
    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.

     

Shaurya's avatar
Shaurya
Icon for Memorable Member rankMemorable 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's avatar
    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_weekid_employeequartileWeeks on quartile
    11q11
    21q12
    31q21
    41q11

     

    Thanks!

    • Shaurya's avatar
      Shaurya
      Icon for Memorable Member rankMemorable 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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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!