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
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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi. It isnt't working fine:

        Thanks