Daily DAX : Day 229 RANK.EQ

 The RANK.EQ function in Power BI's DAX (Data Analysis Expressions) language is used to determine the rank of a value in a column relative to other values in that column. It assigns the same rank to duplicate values (ties) and skips ranks for subsequent values, which is known as "dense ranking."

Syntax

RANK.EQ(<value>, <column>, [<order>])

    value: The value to rank (can be a column reference, measure, or expression).

    column: The column containing the values to rank against.

    order (optional): Specifies the sort order. Use:

        "ASC" (ascending, default) for ranking smallest to largest.

        "DESC" (descending) for ranking largest to smallest.

Return Value

    Returns an integer representing the rank of the specified value in the column.

    If the value doesn't exist in the column, it returns BLANK().

Key Characteristics

    Ties: Duplicate values receive the same rank, and the next distinct value gets the next consecutive rank (e.g., if two values tie for rank 1, the next value gets rank 2, not 3).

    Context Sensitivity: The function operates within the current filter context, so rankings can vary based on slicers, filters, or row contexts in a table.

    Dense Ranking: Unlike RANKX, which supports both dense and non-dense ranking, RANK.EQ always uses dense ranking.

Use Case

The RANK.EQ function is useful when you need to rank items in a dataset, such as:

    Ranking sales performance across regions or employees.

    Identifying top or bottom performers (e.g., top 5 products by sales).

    Creating leaderboards or comparative analyses.

Example

Suppose you have a table Sales with columns Product and Revenue:

Product            Revenue    

A                        100

B                        200

C                        100

D                        300

To rank products by revenue in descending order, you can create a calculated column or measure:

Calculated Column

Rank = RANK.EQ(Sales[Revenue], Sales[Revenue], "DESC")

Result:

Product            Revenue            Rank

A                        100                    3

B                        200                    2

C                        100                    3

D                        300                    1      

    Product D (300) ranks 1.

    Product B (200) ranks 2.

    Products A and C (both 100) tie for rank 3.

Measure

TopRank = RANK.EQ(MAX(Sales[Revenue]), Sales[Revenue], "DESC")

This measure ranks the maximum revenue in the current filter context (e.g., for a specific product in a visual).

Practical Use Case

    Sales Leaderboard: Use RANK.EQ to rank salespeople by their total sales in a report, ensuring ties (e.g., two salespeople with identical sales) are handled consistently.

    Conditional Formatting: Combine with visuals to highlight top-ranked items (e.g., top 3 products).

    Filtering Top N: Use in a calculated table or filter to display only the top N items (e.g., top 5 revenue-generating products).

Notes

    Performance: RANK.EQ can be resource-intensive for large datasets since it evaluates the entire column in the filter context.

    Comparison with RANKX: RANK.EQ is simpler but less flexible than RANKX, which allows ranking across expressions and non-dense ranking. Use RANK.EQ for straightforward column-based ranking.

    Limitations: It only ranks based on a single column's values, not complex expressions. For more advanced scenarios (e.g., ranking by a measure), use RANKX.