By: Siddharth Mehta
Overview
We saw how we can join data from multiple tables as well as navigate data organized in complex forms like a parent-child relationship. In order to sort this data, regular sorting using a clause like ORDER BY may not be enough. We need more advanced sorting functions that can allow us to sort data based on complex criteria. RANKX is one of the important sorting functions that can be used for this purpose. In this chapter we will cover this topic.
Explanation
Let's say we want to rank the internet sales amount for a product against the internet sales amount of all products.
Open SSMS, connect to the tabular SSAS instance where the AdventureWorks tabular model is deployed, and open a new MDX query window. All the perspectives that we saw in the last chapter, will be listed in the Cube pane. Select the Internet Operation perspective in the Cube pane and type the below expression.
EVALUATE (
SUMMARIZE ('Internet Sales'
,[ProductKey]
,"Sum Internet Sales", sum('Internet Sales'[Sales Amount])
,"Rank",RANKX(ALL('Internet Sales'[ProductKey]),SUMX(RELATEDTABLE('Internet Sales'),[Sales Amount]))
)
)
order by [Rank]
Execute the above expression and you should be able to find the results as shown below. In this expression, we are first grouping values by product key and internet sales amount of the respective products. In order to find the rank of these products, we have used the RANKX function. This function expects the table name as the first parameter, expression as the second parameter, and value as the third parameter. It compares the total sales amount of each product against sales amount of all other products.
You can read more about the RANKX function from here to understand more about its functionality.
In this way we can use advanced ranking functions to sort data based on complex criteria.
Additional Information
- Consider reading more about RANX.EQ function which is similar to the RANKX function.
