Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everybody
I have a dataset such as the following:
Customer ID. TransactionDate(UK format)
CUST001. 12/2/24
CUST001. 13/2/24
CUST001. 14/2/24
CUST002. 12/2/24
CUST002. 13/2/24
CUST002. 14/2/24
I'm trying to write DAX or write some M Code to Rank each of the transactions per customer in date order.
The solution I am looking for would be a column which reads
1
2
3
1
2
3
(this is based on my example above)
Many thanks
Solved! Go to Solution.
Using DAX :
TransactionRank =
RANKX(
FILTER(
'YourTableName',
'YourTableName'[Customer ID] = EARLIER('YourTableName'[Customer ID])
),
'YourTableName'[TransactionDate],
,
ASC,
Dense
)
Using M :
= Table.Group(
YourTableName,
["Customer ID"],
{
"Data",
each Table.AddIndexColumn(
Table.Sort(_, {"TransactionDate", Ascending}),
"TransactionRank",
1,
1,
Int64.Type
),
type table
}
)
Using DAX :
TransactionRank =
RANKX(
FILTER(
'YourTableName',
'YourTableName'[Customer ID] = EARLIER('YourTableName'[Customer ID])
),
'YourTableName'[TransactionDate],
,
ASC,
Dense
)
Using M :
= Table.Group(
YourTableName,
["Customer ID"],
{
"Data",
each Table.AddIndexColumn(
Table.Sort(_, {"TransactionDate", Ascending}),
"TransactionRank",
1,
1,
Int64.Type
),
type table
}
)
Thank you so much
Welcome ! Good luck with M it is a little bit tricky sometime 😄
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |