Concept Breakdown
The RANKX function is an iterator that evaluates an expression for each row in a specified table and then ranks the resulting values. When ranking categories (like TV series) based on an aggregated measure (like total viewers), it's crucial to correctly define the table over which to iterate (often using ALL or ALLSELECTED) and to use CALCULATE to ensure proper context transition when summing values for each category within the RANKX iteration. This allows the aggregation to be correctly calculated for each row context created by RANKX.
What You Will Learn
Understand when to use RANKX in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a advanced-level task and verify the result with a hidden answer check.
Practice in Power BI
The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.
Download and unzip the provided Power BI file and open it.
In Power BI Desktop, create a new measure.
Name the new measure 'Rank expression'.
Write a DAX formula using the `RANKX` function to rank each distinct 'Series' (from the `Series[Series]` column) by its total 'Viewers (m)'.
Ensure the ranking assigns rank 1 to the series with the highest viewing figures (descending order).
Use the DENSE argument for ties to ensure consecutive ranks.
Place the 'Series' column and your new 'Rank expression' measure into a table or matrix visual to observe the results.
Starter DAX
Rank of Series = RANKX(<table>, <expression>, <value>, <order>, <ties>)Expected Outcome
A measure that assigns a rank to each Great British Bake Off series. The series with the highest total viewing figures should be ranked 1st, with subsequent ranks for lower viewing figures. The ranks should generally follow the series numbers but show a clear divergence or 'blip' around the transition to Channel 4.