Rank Function and Combinations

Not only can we use TopCount to retrieve the top n members based on a measure, we can also use the Rank function to assign a sequential ranking number. Rank allows you to assign the specific order number of each result. Just like with TopCount, you’ll want to use a named set to fully optimize a Rank statement.

For example, suppose we want to rank states by Internet Sales Amount, showing sales for those states with sales that are non-zero. We need to do two things. First, we create a named set that sorts the states by Internet Sales Amount, and then we create a calculated member that ranks each state by the ordered set. The full query, which we’ll break down later, is shown here. The query results are shown in Figure 10-22.