Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
How I can create calculated column Sequence numbers of orders date by customer id and date of purchase ?
Solved! Go to Solution.
Hi, @Anonymous
You still need to add an index column, because there are 2 rows that are the same date and need to be sorted.
Rank =
IF ( OR ( [Order type] = "Return", [Store] = "D" ), BLANK (),
CALCULATE ( COUNT ( 'Table'[Customer Email] ),
FILTER ( 'Table',
[Order type] <> "Return" && [Store] <> "D"
&& [Customer Email] = EARLIER ( 'Table'[Customer Email] )
&& [Index]<=EARLIER('Table'[Index])
)
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I just want to do this. When one date for ranks one time. For Special Stores and Return skips ranking.
Hi, @Anonymous
You still need to add an index column, because there are 2 rows that are the same date and need to be sorted.
Rank =
IF ( OR ( [Order type] = "Return", [Store] = "D" ), BLANK (),
CALCULATE ( COUNT ( 'Table'[Customer Email] ),
FILTER ( 'Table',
[Order type] <> "Return" && [Store] <> "D"
&& [Customer Email] = EARLIER ( 'Table'[Customer Email] )
&& [Index]<=EARLIER('Table'[Index])
)
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
You can try the following methods.
Column:
Rank =
CALCULATE ( COUNT ( 'Table'[Customer Email] ),
FILTER ( 'Table',
[Customer Email] = EARLIER ( 'Table'[Customer Email] )
&& [Date of Purchase] <= EARLIER ( 'Table'[Date of Purchase] )
&& [Index] <= EARLIER ( 'Table'[Index] )
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @Mikelytics . But it doesnt work, when one email repaits (2+ times) in one date of purchase
Jo @Anonymous ,
question is, what is your expected result when you have two purchases on one date? I added two new versions where I actually used the RANKX function is one of them what your are looking for?
1. RANKX with Dense
Rank with Rank Function (Dense) =
var _Email = [Customer Email]
var _DateOfRow = [Date of Purchase]
RETURN
RANKX(
FILTER(SampleTableRank,SampleTableRank[Customer Email]=_Email),
[date of Purchase],,ASC,Dense
)
2. RANKX with Skip
Rank with Rank Function (Skip) =
var _Email = [Customer Email]
var _DateOfRow = [Date of Purchase]
RETURN
RANKX(
FILTER(SampleTableRank,SampleTableRank[Customer Email]=_Email),
[date of Purchase],,ASC,Skip
)
if this does not work please also take a look on the approach provided by @v-zhangti which provides another possible result.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hi @Anonymous
Please try the following
Rank =
var _Email = [Customer Email]
var _DateOfRow = [Date of Purchase]
RETURN
CALCULATE(
COUNTROWS(SampleTableRank),
ALL(SampleTableRank),
SampleTableRank[Customer Email] = _Email,
SampleTableRank[Date of Purchase] <= _DateOfRow
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.