Ranking with RANKX

1. Introduction to RANKX and its Core Challenge

At its heart, RANKX is an iterator function. It goes through a table you provide, evaluates an expression for each row, and then returns the rank of a specific value within that list.

The basic syntax is:
RANKX(<table>, <expression>, [<value>], [<order>], [<ties>])

The two mandatory arguments are:

1. <table>: The list of items you want to rank.
2. <expression>: The calculation used to determine the rank (e.g., [Total Sales]).

The most common mistake people make is with the <table> argument. If you simply provide a column name, your measure will likely return 1 for every row. This happens because of the filter context created by your visual. Each row in a table filters the data down to just that one item, so RANKX tries to rank the item against a list containing only itself!

To solve this, you must use a function like ALL or ALLSELECTED to provide RANKX with the complete list of items it needs for the ranking.

The following video from SQLBI provides a brilliant demonstration of this exact problem and its solution.

2. The RANKX Algorithm: How It Really Works

To truly master RANKX, you need to understand its internal algorithm, which aligns with your preference for knowing the "why." RANKX doesn't just magically produce a number; it follows a clear, three-step process.

1. Create a Lookup Table: RANKX first iterates over the table provided in its first argument (e.g., ALL('Product'[Brand])). For each item in that list, it calculates the expression from the second argument (e.g., [Sales Amount]). The result is a temporary, internal table (a "lookup table") of all the values to be ranked.
2. Get the Current Value: It then calculates the same expression in the original filter context of the visual's cell to get the value for the current item (e.g., the sales for "Contoso").
3. Find the Position: Finally, it searches for the current item's value within the sorted lookup table and returns its position.

This clear, step-by-step article from SQLBI walks through this exact algorithm. Reading it will solidify the concept you just saw in the video.

3. Building a Practical and Dynamic Ranking Measure

Now, let's put this theory into practice by building a measure to rank products by their sales. For most business reports, you want the ranking to be dynamic. If a user filters the report to a specific region or year, the ranking should adjust to show the rank within that selection. For this, ALLSELECTED is more appropriate than ALL.

Let's create the measure. First, ensure you have your base [Total Sales] measure:
Total Sales = SUM('Sales'[Revenue])

Now, for the ranking measure, we'll use ALLSELECTED and also apply the best practice of using variables that you learned in the previous lesson.

Product Rank = 
// Using ISINSCOPE to prevent the rank from appearing in the grand total row
IF(
    ISINSCOPE('Product'[Product Name]),
    RANKX(
        ALLSELECTED('Product'[Product Name]),
        [Total Sales]
    )
)

Why ALLSELECTED?

• ALL('Product'[Product Name]): Ranks a product against all products in the entire table, regardless of slicers.
• ALLSELECTED('Product'[Product Name]): Ranks a product against only the products that are currently visible in the visual (i.e., it respects external slicers). This is usually the desired interactive behavior.

To see this in action, the SQLBI video has a concise segment demonstrating the difference.

4. Handling Ties: SKIP vs. DENSE

What happens if two products have the exact same sales? The optional fifth argument of RANKX lets you decide how to handle these ties.

• SKIP (Default): If two items tie for 2nd place, the next rank will be 4th. The rank numbers are skipped to account for the number of tied items.
• DENSE: If two items tie for 2nd place, the next rank will be 3rd. This ensures there are no gaps in the rank numbering, which is often preferred in business reports.

Let's look at a visual comparison.

SKIP Ranking:

DENSE Ranking:

The following video segment provides a practical example of how to implement SKIP and DENSE and what the results look like.

Here is how you would write the DENSE version of our measure:

Product Rank (Dense) = 
IF(
    ISINSCOPE('Product'[Product Name]),
    RANKX(
        ALLSELECTED('Product'[Product Name]),
        [Total Sales],
        , // Leaving the <value> argument blank
        DESC, // Sorting from highest sales to lowest
        DENSE // Handling ties without skipping ranks
    )
)

Conclusion

You have now dissected one of the most important functions in DAX. By understanding how RANKX interacts with the filter context and how its internal algorithm works, you can confidently build robust and accurate ranking measures.

Key Takeaways:

• RANKX is an iterator function that ranks items based on an expression.
• The dreaded "column of 1s" is caused by filter context. You must use ALL or ALLSELECTED in the first argument to give RANKX the correct list to rank against.
• ALLSELECTED creates dynamic rankings that respond to user selections in slicers.
• RANKX works by building a "lookup table" of values, getting the current item's value, and finding its position in the sorted list.
• The DENSE argument is often preferable for business reports as it prevents gaps in ranking when ties occur.

Preview of the Next Lesson:
Now that you can rank items, we will move on to another powerful analytical technique. In the next lesson, you will learn to calculate rolling totals over dynamic periods, such as a "rolling 90-day sales" figure. This involves using the DATESINPERIOD function and will further build on your growing mastery of advanced DAX.