Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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_week | id_employee | quartile | Weeks on quartile |
1 | 1 | q1 | 1 |
2 | 1 | q1 | 2 |
3 | 1 | q2 | 1 |
4 | 1 | q1 | 1 |
3 | 2 | q3 | 1 |
4 | 2 | q4 | 1 |
5 | 2 | q4 | 2 |
2 | 3 | q2 | 1 |
3 | 3 | q2 | 2 |
4 | 3 | q2 | 3 |
5 | 3 | q2 | 4 |
6 | 3 | q1 | 5 |
Thanks in advance!
Solved! Go to Solution.
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
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
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.
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
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.
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!
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
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 @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!
Hi! It works partially, because I need the counter to be reseted everytime a driver changes from quartile, for exaple:
id_week | id_employee | quartile | Weeks on quartile |
1 | 1 | q1 | 1 |
2 | 1 | q1 | 2 |
3 | 1 | q2 | 1 |
4 | 1 | q1 | 1 |
Thanks!
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?
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 @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
Hi. It isnt't working fine:
Thanks
Hi @Anonymous ,
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_week | id_employee | quartile | Weeks on quartile |
1 | 1 | q1 | 1 |
2 | 1 | q1 | 2 |
3 | 1 | q2 | 1 |
4 | 1 | q1 | 1 |
Thanks!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
67 | |
49 |
User | Count |
---|---|
140 | |
113 | |
104 | |
64 | |
60 |