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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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