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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Daretoexplore
Frequent Visitor

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

1 ACCEPTED SOLUTION
AmiraBedh
Resident Rockstar
Resident Rockstar

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
    }
)

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

3 REPLIES 3
AmiraBedh
Resident Rockstar
Resident Rockstar

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
    }
)

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Thank you so much

Welcome ! Good luck with M it is a little bit tricky sometime 😄


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.