Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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
Shaurya's avatar
Shaurya
Icon for Memorable Member rankMemorable Member
4 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
    4 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
      4 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
        4 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!