Power BI DAX Functions – RANK and ROWNUMBER

Problem

Two new functions, RANK and ROWNUMBER, have been added to DAX with the April 2023 Power BI updates. These two much anticipated DAX window functions are a significant improvement by making it much easier to return a number indicating the rank for the current context within the specified partition, sorted by the specified order.

How can you use the RANK and ROWNUMBER DAX window functions correctly and efficiently? What’s the difference between them? In this tip, we’ll give an overview of the RANK and ROWNUMBER functions and the sample codes demonstrating their syntax and usage in various contexts.

Solution

This tip will provide fundamental information on DAX RANK and ROWNUMBER functions, including the syntax and usage, and demonstrate their similarities and differences using sample data and codes.

DAX RANK Function

Before RANK was introduced, RANKX had been the primary DAX ranking function for a long time. But RANKX has been known for having serious limitations. First, although only on rare occasions, when ranking on decimal numbers, RANKX may produce wrong results. The article DAX RANKX Function Behaviors Lead to Incorrect Results and Corrections describes this issue in detail. Second, RANKX is only capable of ranking using a single expression. Often, it is necessary to obtain a ranking based on multiple columns, and developers have to write complex code and use some clever tricks to get that done while using RANKX.

The new DAX RANK function makes it incredibly easy to get a ranking using multiple columns, allowing rankings based on complex sorting criteria.

Let’s first look at the syntax of the RANK function:

Syntax

RANK ( [<ties>][, <relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>] )

Parameters

Source – https://learn.microsoft.com/en-us/dax/rank-function-dax

Sample Data Set and Data Model

To demonstrate DAX RANK and ROWNUMBER functions, I created a small sample sales data set for a fictional office supply retail chain. The data set includes a Fact (Sales) and 2 Dimension tables (Stores and Products). The data model is shown in the image below.

You can download the Excel dataset and Power BI workbook.

Next, we’ll use the data set to demonstrate how to use the new DAX RANK function.

We have the following product data, including ListPrice:

If we rank the ListPrice from the highest to the lowest using the RANKX function, we will get the following results:

As you can see, RANKX is capable of ranking ONLY one column, i.e., the ListPrice here, because Products 4 and 5 have the same price (4.50), so they are tied with rank 7 in the list.

The new DAX RANK function provides sorting by multiple columns as a native feature. We can easily solve the above tie problem by ordering ListPrice AND ProductName. We can easily create a new measure, ListPrice_RANK, by taking advantage of the enhanced ORDERBY function, which now supports sorting by multiple columns as below:

ListPrice_RANK = RANK(SKIP, ALLSELECTED(Products),ORDERBY(Products[ListPrice], DESC, Products[ProductName], ASC)) 

We get the following ranking without the tie in the list:

DAX ROWNUMBER Function

Every database developer and data analyst know the importance of row number. Row numbers enable you to pinpoint specific rows in a table and easily sort and filter data. However, keeping track of individual rows without row numbers can be challenging. The new DAX ROWNUMBER function brings you an amazing new tool to efficiently and effortlessly identify unique rows and track their movement as you filter, sort, or search data within the table.

The syntax of ROWNUMBER is very similar to the RANK function:

Syntax

ROWNUMBER ( [<relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>] ) 

Parameters

Source – https://learn.microsoft.com/en-us/dax/rownumber-function-dax

However, ROWNUMBER differs from the RANK function: in case of a tie and if we still define order by only one column, RANK will assign the same RANK number multiple times. But ROWNUMBER will try to avoid that by finding the least number of additional columns required to uniquely identify every row and append these new columns to the ORDERBY clause. Only after it cannot uniquely identify every row, ROWNUMBER will return an error.

Let’s use the same PriceList sample used above to demonstrate the difference.

First, let’s create a new measure, ListPrice_RANK2. This time, we only order by one column:

ListPrice_RANK2 = RANK(SKIP, ALLSELECTED(Products),ORDERBY(Products[ListPrice], DESC)) 

We get the following ranking: both products 4 and 5 get ranking number 7 because they have the same ListPrice (4.50):

Next, let’s create a new measure called ListPrice_ROWNUMBER. We also define one column in the ORDERBY clause:

ListPrice_Rownumber = ROWNUMBER(ALLSELECTED(Products),ORDERBY(Products[ListPrice],DESC)) 

This time, we get the following ranking: products 4 and 5 get ranking number 7 and 8, respectively, even though they have the same ListPrice (4.50):

Here, the DAX ROWNUMBER function did some magic work in the background. It sought the least number of additional columns, which was column ProductKey here, to uniquely identify every row and append this new column to the ORDERBY clause. As a result, products 4 and 5 get a unique ranking number 7 and 8, even though they have the same ListPrice.

DAX RANK and ROWNUMBER Function Full Syntax Sample

We have thus far used most of the parameters in the DAX RANK and ROWNUMBER functions. But how about the last three parameters: blanks, partitionBy, and matchBy? Let’s use them now to demonstrate the roles they play.

blanks

The parameter between orderBY and partitionBy is blanks, an enumeration that defines how to handle blank values when sorting. The supported values are:

• DEFAULT (the default value): The behavior for numerical values is blank values are ordered between zero and negative values. The behavior for strings is blank values are ordered before all strings, including empty strings.
• FIRST: Blanks are always ordered on the beginning, regardless of ascending or descending sorting order.
• LAST: Blanks are always ordered on the end, regardless of ascending or descending sorting order.

partitionBy and matchBy

The last two parameters. PARTITIONBY and MATCHBY, together with ORDERBY, are special functions that can only be used with DAX Window functions such as RANK and ROWNUMBER.

The PARTITIONBY function is a way to divide the table into parts. When the MATCHBY function is used in a Window function, the DAX Engine verifies at runtime that the specified MATCHBY columns can serve as unique identifiers for each row in every partition.

We have the following ProductSales data:

We can use the partitionBy parameter to rank the product sales within each ProductCategory. To do that, we can create a measure, ProductSales_Rank by specify PartitionBy. For demonstration purposes, we will use all the parameters in the measure, but we can omit matchBy and get the same result.

ProductSales_Rank = RANK(SKIP, ALLSELECTED(Products), ORDERBY(CALCULATE(Sum(Sales[SalesAmount])), DESC, Products[ProductName], DESC), Last, PARTITIONBY(Products[ProductCategory]), MATCHBY(Products[ProductKey]))
 

The result is shown below:

In the result, the ProductSales_Rank is based on the Sum of SalesAmount within the ProductCategory. For example, in the ‘Binder’ Category, the product ‘Cardinal Grain-Textured Covers’ has sales of 225, which is ranked as 1, higher than the product ‘Avery Slant Ring Binders’ with sales of 87.

Similarly, we can create a new measure, ProductSales_Rownumber, by using all the parameters, as shown below:

ProductSales_Rownumber = ROWNUMBER(ALLSELECTED(Products), ORDERBY(CALCULATE(Sum(Sales[SalesAmount])), DESC), Last, PARTITIONBY(Products[ProductCategory]), MATCHBY(Products[ProductKey]))
 

The result is shown below:

Next Steps

For more information on DAX RANK and ROWNUMBER functions, refer to the links below:

• Power BI April 2023 Feature Summary
• Understanding ORDERBY, PARTITIONBY, and MATCHBY function
• Power BI – Window Function