The Surprising Benefits of Choosing RANK DAX Over RANKX for Power BI: My Personal Experience

In this writing, I like to share my recent discoveries regarding one of the two new DAX functions.

Recently, I saw two new DAX functions – RANK and ROWNUMBER – while exploring the April 2023 Power BI updates. Eager to learn more about them, I scoured YouTube channels and Microsoft documentation for insights from experts. However, as these functions were very new, no YouTube videos were available yet, and I had to rely solely on the documentation.

Without delay, I decided to test the RANK DAX function myself.

While I was initially surprised to find it classified as a filter function, rather than a statistical one like RANKX, I was excited to see what it could do.

One of the first things I noticed was that, unlike RANKX, RANK did not require me to use an IF statement in conjunction with my DAX measure to exclude the result on the total row of a table visualization.

Syntax

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

-> Returns the ranking for the current context within the specified partition, sorted by the specified order. If a match cannot be found then then rank is blank.

Documentation link: RANK function (DAX) - DAX | Microsoft Learn

In fact, I found that RANK's ability to return a blank result when a match could not be found made it even more useful in some cases.

In addition, I found that RANK outperformed RANKX in terms of SE queries in my sample data model. Using RANKX, I occasionally encountered unnecessary results, but RANK's blank return helped me avoid such issues.

Overall, I was thrilled with the RANK DAX function and look forward to discovering more about its capabilities in future updates.

First finding.

Upon implementing the RANK DAX function into my datamodel and visualizing the measure in a table, I immediately discerned a significant disparity:

the TOTAL row in the table visualization produced no results. According to the documentation, RANK Returns the ranking for the current context within the specified partition, sorted by the specified order. If a match cannot be found then then rank is blank.

When utilizing RANKX in certain cases, I previously needed to write an IF statement to prevent the RANKX result from appearing in the TOTAL row. However, with the RANK DAX function, there's no need for additional conditional statements when generating ranking results.

Second finding.

In certain scenarios, when utilizing the RANKX DAX function, I had to create an IF statement to check if the expression is blank, and if so, return a blank value; otherwise, provide the RANKX result.

In contrast, the RANK DAX function returns a blank value anyway, if a match cannot be found.

Third finding.

Based on my sample datamodel, I have observed that the RANK DAX function performs better in terms of SE Queries than RANKX.

Specifically, my fact table contains only 14 rows, while my dimension table has 1,000,001 customers. In some cases, RANKX can produce unnecessary results due to its nature of returning results even when a match cannot be found. However, with the RANK DAX function, the results are blank when a match cannot be found, which can help to eliminate these unnecessary results.

Sales_simple (fact table): 14 rows

Country dimension table: 4 rows

Customer dimension table: 1,000,001 rows

Datamodel

By using the above sample, I created the below visualization.

Table visualization

After implementing the if condition ( if the expression is not blank, show the result, else blank) into the two RANKX DAX functions, I could manage to have the below result.

RANKX Simple skip: = IF ( [Quantity simple:] <> BLANK (), RANKX ( SUMMARIZE ( ALL ( Sales_simple ), Country[Country], Customer[Customer] ), [Quantity simple:], , DESC, SKIP ) )

RANKX Simple dense: = IF ( [Quantity simple:] <> BLANK (), RANKX ( SUMMARIZE ( ALL ( Sales_simple ), Country[Country], Customer[Customer] ), [Quantity simple:], , DESC, Dense ) )

RANK Simple skip: = RANK ( SKIP, SUMMARIZE ( ALL ( Sales_simple ), Country[Country], Customer[Customer] ), ORDERBY ( [Quantity simple:], DESC ) )

RANK Simple dense: = RANK ( DENSE, SUMMARIZE ( ALL ( Sales_simple ), Country[Country], Customer[Customer] ), ORDERBY ( [Quantity simple:], DESC ) )

I checked the performance in DAX Studio.

RANKX result:

RANK result:

To summarize, the RANK DAX function is a powerful addition to the DAX language in Power BI, with several advantages over the RANKX function. It eliminates the need to write additional conditions in certain cases when using RANKX, as it automatically returns a blank for the total row when no match is found. It also simplifies DAX expressions, as it doesn't require writing additional conditions for blank expressions. Furthermore, in some cases, it can provide better performance compared to RANKX, particularly when dealing with large dimension tables. Overall, the RANK DAX function is a valuable tool for data analysts and Power BI users, and it is worth exploring and incorporating into your DAX formulas.

Even though I just started to find out one benefit, and shared one piece of information, I hope that the information I have shared about the RANK DAX function will inspire you to explore its capabilities further.

I hope this helps to have more fun exploring the RANK DAX function in Power BI.