Forum Discussion

Daretoexplore's avatar
Daretoexplore
Advocate I
2 years ago
Solved

Ranking query

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

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    Using DAX : 

    TransactionRank = 
RANKX(
    FILTER(
        'YourTableName',
        'YourTableName'[Customer ID] = EARLIER('YourTableName'[Customer ID])
    ),
    'YourTableName'[TransactionDate],
    ,
    ASC,
    Dense
)

     

    Using M :

    1. Go to "Home" > "Edit Queries" to open the Power Query Editor.
    2. Select the table you want to modify.
    3. Go to "Add Column" tab > "Index Column" > "Custom Index".
    4. Start the index from 1 and increment by 1.
    5. Before applying the custom index, you might want to ensure your rows are sorted by "Customer ID" and then by "TransactionDate". You can do this by selecting the "Sort Ascending" option on the "Customer ID" column and then doing the same for the "TransactionDate" column.
    6. Use the "Group By" feature to group by "Customer ID", and within each group, sort by "TransactionDate" and then add the index column. This would require some advanced M code manipulation.
    = Table.Group(
    YourTableName, 
    ["Customer ID"], 
    {
        "Data", 
        each Table.AddIndexColumn(
            Table.Sort(_, {"TransactionDate", Ascending}), 
            "TransactionRank", 
            1, 
            1, 
            Int64.Type
        ), 
        type table
    }
)

     

3 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    Using DAX : 

    TransactionRank = 
RANKX(
    FILTER(
        'YourTableName',
        'YourTableName'[Customer ID] = EARLIER('YourTableName'[Customer ID])
    ),
    'YourTableName'[TransactionDate],
    ,
    ASC,
    Dense
)

     

    Using M :

    1. Go to "Home" > "Edit Queries" to open the Power Query Editor.
    2. Select the table you want to modify.
    3. Go to "Add Column" tab > "Index Column" > "Custom Index".
    4. Start the index from 1 and increment by 1.
    5. Before applying the custom index, you might want to ensure your rows are sorted by "Customer ID" and then by "TransactionDate". You can do this by selecting the "Sort Ascending" option on the "Customer ID" column and then doing the same for the "TransactionDate" column.
    6. Use the "Group By" feature to group by "Customer ID", and within each group, sort by "TransactionDate" and then add the index column. This would require some advanced M code manipulation.
    = Table.Group(
    YourTableName, 
    ["Customer ID"], 
    {
        "Data", 
        each Table.AddIndexColumn(
            Table.Sort(_, {"TransactionDate", Ascending}), 
            "TransactionRank", 
            1, 
            1, 
            Int64.Type
        ), 
        type table
    }
)