Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aguuzdo
Helper I
Helper I

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_weekid_employeequartileWeeks on quartile
11q11
21q12
31q21
41q11
32q31
42q41
52q42
23q21
33q22
43q23
53q24
63q15

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
ImkeF
Super User
Super User

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




Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ImkeF
Super User
Super User

Hello @aguuzdo ,
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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

11 REPLIES 11
ImkeF
Super User
Super User

Hello @aguuzdo ,
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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks for your time, that works perfectly!

ImkeF
Super User
Super User

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




Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Shaurya
Memorable Member
Memorable Member

Hi @aguuzdo,

 

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:

 

Screenshot 2022-10-07 235208.jpg

 

Works for you? Mark this post as a solution if it does!

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!

Hi @aguuzdo,

 

I can see that the numbers in my previous reply match with those in your post. Can you please elaborate this driver change scenario?

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!

Hi  @aguuzdo ,

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​

yingyinr_0-1665387596764.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi. It isnt't working fine:

aguuzdo_0-1665411594427.png

Thanks

ImkeF
Super User
Super User

Hi @aguuzdo ,
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
)



Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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!

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors