Question

How can I use DAX to generate a dynamic ranking that changes based on selected filters?
I need a Power BI measure that ranks categories (such as products or salespeople) dynamically, ensuring that the ranking updates based on user-applied slicers. What is the best approach using DAX functions like RANKX() and ALLSELECTED() to maintain accurate rankings when filters are applied?

Answer 1

You can create a dynamic ranking measure using RANKX() and ALLSELECTED() to ensure rankings update based on user-applied slicers.

1. Create the Dynamic Ranking Measure

DynamicRank =
RANKX(
    ALLSELECTED('Sales'[Category]),  -- Respects active slicers
    [Total Sales], 
    , DESC, DENSE
)

Clarification: ALLSELECTED('Sales'[Category]) restricts the changes in rank to only the current filtering context (say, selected regions or time frame).

DESC arranges it in descending order (high sale up to the last recorded sale).

DENSE means there are no holes in the rank (1,2,3 instead of 1,2,4).

How It Works

When the user filters for Region, ranking changes for that particular Region.

When no filters have been applied, the ranking is that of overall sales.