Grouping and Ranking in Power BI

By Akash Rajendran Nair

Introduction

When working with structured data, one common challenge is grouping records based on shared attributes and then ranking them within those groups. In Power BI, we often need to analyse relationships where individuals belong to multiple categories over time.

In this example, we focus on mentor-student relationships across clubs and aim to determine the first club a student joined under each mentor.

Key Concepts We’ll Cover

• Grouping Data: Using filters to ensure rankings occur within a specific subset.
• Ranking Within Groups: Using RANKX to identify order based on a specific metric (in this case, a combination of Joined Date and Left Date).

By leveraging DAX functions like FILTER, RANKX and EARLIER, we can dynamically analyse our data and present meaningful insights.

Understanding the Scenario

The Table

We have a Club List table containing the following columns:

• Student Name – The name of the student.
• Mentor Name – The mentor guiding the student.
• Club Name – The name of the club.
• Joined Date – The date the student joined the club.
• Left Date – The date the student left the club.

Key Considerations:

• A student can join multiple clubs at the same time under the same mentor.
• However, students do not leave clubs at the same time under each mentor; the time period in each club will be different.
• If a student has multiple clubs joined on the same date, amongst those, the first club they left is considered the first club for ranking purposes.
• If a student's Left Date is missing, we assume they are still in the club

An Example Scenario of Student Andrea Chase and Mentor Jennifer Thompson is given below.

Here, the student joined with the mentor on Club 1 and Club 2 on 13/12/2023, but left Club 1 on 31/7/2024. He rejoined Club 1 again on 17/8/2024 with the same mentor. He joined Club 3 with another mentor on 26/3/2025. So the rankings should be as follows.

Building the Power BI Solution

Creating the Calculated column Club Rank:

Club Rank =  

RANKX(

    FILTER(

        club_list,

        club_list[Student Name] = EARLIER(club_list[Student Name]) &&

        club_list[Mentor Name] = EARLIER(club_list[Mentor Name])

    ),

    club_list[Joined date] + 

    IF(ISBLANK(club_list[Left date]), TODAY(), club_list[Left date]) / 100000,  

    ,  

    ASC,  

    Skip  

)

Breakdown of The DAX Query:

1. RANKX Function

The RANKX function ranks entries based on a given numerical expression. It assigns ranks starting from 1 for the smallest value when using ASC (ascending order).

2. FILTER Function

FILTER(

    club_list,

    club_list[Student Name] = EARLIER(club_list[Student Name]) &&

    club_list[Mentor Name] = EARLIER(club_list[Mentor Name])

)

The FILTER function creates a subset of club_list:

• It selects only rows where the Student Name and Mentor Name match the current row being evaluated.
• This ensures ranking is applied within each student-mentor group, rather than across the entire dataset.

3. Ranking Expression

club_list[Joined date] + 

IF(ISBLANK(club_list[Left date]), TODAY(), club_list[Left date]) / 100000

This determines the ranking criteria based on:

1. Joined Date → The primary sorting factor (earliest dates get the lowest rank).
2. Left Date → Acts as a secondary sorting factor when there’s a tie.

• If the Left Date is blank, it is replaced with TODAY(), meaning active clubs use today’s date for ranking.
• The Left Date is divided by 100000 to prevent it from overriding the primary sorting factor (Joined Date).
• Since dates are stored as numeric values, dividing by a large number ensures Left Date only has a minor impact on ranking.

4. Sorting Direction (ASC)

• Ensures earlier Joined Dates get lower ranks (rank 1 is the first club).
• If multiple clubs have the same Joined Date, the earliest Left Date determines rank order.

5. Handling Duplicate Ranks (Skip)

• Ensures that if multiple clubs have the same ranking, the next rank is skipped instead of assigning the same rank.
• This prevents duplicate rankings and ensures clarity in identifying the first club

Final Outcome:

After applying this DAX to create a Calculated Column:

1. Students will be grouped under each mentor.
2. Clubs will be ranked based on Joined Date.
3. If two clubs were joined on the same date, the earliest Leaving Date determines ranking priority.
4. If the Leaving Date is missing, today's date is used for ranking.

By filtering the table visual to show Club Rank = 1. We successfully identified the first club a student joined and left under a mentor.