Elevate DAX: Top 3 Ways To Generate Rank In Power BI
A Journey Through RANKX, RANK, and ROWNUMBER With Examples And Code Snippets
Welcome to the inaugural blog post of my Elevate DAX series! In this series, I aim to simplify various DAX functions through easy explanations and examples.
We’ve all encountered situations where users want to highlight their top products, geographies, or rank them in a specific order. In this blog, I’ll cover different approaches of generating ranks in Power BI.
Introduction
Ranking is one of the most common and frequent calculations in Power BI reports, but it sometimes becomes quite complex. There are a couple of ways to generate rank using DAX. We’ve all used RANKX, right? Well, now, with the magic of window functions in Power BI, there are a few more tricks up our sleeves.
Through this blog, I want to make things super clear and easy. I’ll be using the sample financial data from Power BI, so it’s hands-on and real.
Ready to dive into simple ways to generate rank with DAX? Let’s get started!
Method 1: RANKX
RANKX stands out as one of the primary functions for generating rankings in DAX, and it remains a powerful and effective method to this day. To use the function effectively, one requires a proper understanding of how it works; otherwise, its use becomes intimidating.
Let’s break it down with a simple example. Imagine you want to rank products based on their generated profits in financial data. Here’s a basic syntax:
Now let’s dissect RANKX calculation in detail. The first argument, ALLSELECTED, provides a table of all product values visible in the matrix column. If we opted for VALUES/ALL instead of ALLSELECTED, the filter context wouldn’t work correctly. We’d end up with all ranks as 1 for VALUES and ALL would give rankings based on all products in the data, regardless of how many are shown in the matrix.
RANKX then goes through this table of all products, computing [TotalProfit] for each row. During this iteration, there’s an active row context of the product column. Since we’re using the [TotalProfit] measure, involving a CALCULATE function, it transforms Row Context to Filter Context. If there were no measure, we’d need to wrap the calculation inside CALCULATE to achieve the same. Something like this:
Once [TotalProfit] is calculated for each product, a lookup table with all the values of [TotalProfit is ready with RANKX. As defined by us in RANKX, the lookup table is then sorted in DESC order, which is also the default sorting order.
The Lookup table looks like this.
Now, in the second argument of RANKX, we’ve defined the [TotalProfit] measure. RANKX calculates [TotalProfit] for each row in the current filter context. For example, for Velo, it might be 5,59,260.42, for Amarilla, 6,05,802.45, and so on.
Next, the RANKX function looks for 5,59,260.42 in the already sorted Iterated table from the first step. It finds this value in the fourth place, hence the result is 4. Similarly, for 6,05,802.45, the result is 3, and so on.
Once rank values are ready, they are either generated in SKIP or DENSE manner, depending on what we’ve defined inside RANKX. SKIP skips the next rank in case of any tie, and DENSE gives the same rank to tied values, maintaining a continuous order.
And this is all about how the ranks are computed!!!
But wait, there is one more thing that is to be understood.
If looked closely in the table visual below, we can see that there is a rank value in the Total row also. This is not ideal as its Total and we should get rid of it.
To address this, we can make use of HASONEVALUE or ISINSCOPE function. ISINSCOPE will confirm if the iteration is in the same scope of the Product row, which in the case of the Total row, would be false. Similarly, HASONEVALUE will let us know if there is only one row selected during calculation, which again for the Total row would be false. Hence we would get a Blank value for rank column in the Total row. .
And with this, we get the most accurate expression to generate a rank using RANKX. See below:
Method 2: RANK
The RANK function, a newcomer in the scene, operates similarly to the window functions seen in SQL. RANK simplifies a couple of things when compared to RANKX. As you might recall, RANKX tends to generate a rank equal to 1 for Total and SubTotal rows, which is not the expected behavior. Sure, as discussed earlier, we can resort to HASONEVALUE or ISINSCOPE to achieve what we want, but this adds an extra layer of complexity that we’d rather avoid.
Additionally, when it comes to sorting rankings based on multiple columns, RANKX falls short. This is where RANK steps in, addressing these issues in a straightforward manner without demanding much understanding on our part.
Take a glance at the screenshot below, illustrating how RANK effortlessly generates ranks for products based on profit generated (shown in blue).
Most of the parameters in RANK are akin to those in RANKX, with the exception of the sorting attribute. In RANK, we must specify the sort order using the ORDERBY function. In this instance, ORDERBY takes TotalProfit as the first argument, forming the basis for sorting. The second argument for ORDERBY is DESC, defining the sort order.
As mentioned earlier, it’s possible to include multiple ORDERBY columns in this RANK window function, like the example below:
And that’s how the rank is generated using the RANK function. As we can see, it’s simpler to use compared to RANKX.
However, there are certain scenarios where RANKX is preferred, and I’ll discuss on those in another blog post.
Method 3: ROWNUMBER
Like RANK, ROWNUMBER is another window function, and it’s relatively new on the block. As the name implies, this function is primarily designed to generate row numbers, but we can also leverage it to create rankings.
Let’s explore how we can achieve that. Take a look at the screenshot below (shown in purple):
As depicted, we’ve set the first argument as a table containing all product values in the matrix. This establishes the relation argument, assuming there are only unique values for products. This is a departure from RANK, where duplicate product values are allowed. ROWNUMBER, however, insists on uniqueness during execution and triggers an error if duplicate values are detected.
We can see that ROWNUMBER is simpler and more straightforward in its application. And hence, when duplicates are not permissible and a straightforward row-based ranking suffices, ROWNUMBER could be the preferred choice.
Conclusion
In this kickoff of the Elevate DAX series, I’ve tried to demystify ranking in Power BI, exploring RANKX, RANK, and ROWNUMBER with examples. As you navigate these DAX functions, remember that the choice between these, depends on your data’s unique needs.
Feel free to share your preferences and insights in the comments!
Stay tuned for the next topics in the Elevate DAX series. Get ready for an exciting ride in the world of Power BI reporting, where we simplify DAX for a more powerful analytics experience.
Don’t forget to subscribe to
and join our Power BI community
