A Deeper Understanding Of Advanced RANKX

This tutorial will go through an advanced example on the DAX function RANKX in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

This is a breakout session from the Learning Summit Workshop in Enterprise DNA Online.

The RANKX function gives you valuable insights when you use it for a range of different contexts in your report page. However, it can also be one of the most confusing functions to use in Power BI.

It can give you different results that seem to be correct, when in fact, theyâ€™re wrong. If youâ€™re not careful, youâ€™ll end up reworking the entire report.

Relationship Between RANKX And Contexts

In this visualization, you can see the County Ranking, States Ranking, and State Regions Ranking.

Managing the context of the calculation can be difficult if you want to see them all in one matrix.

Letâ€™s look at the table.

You can see it has information on the State Regions, State, and County.

For this example, you need to work out the ranking of the Counties.

If you just have the County, County Ranking, and Total Sales in the table, you get the correct ranking results:

But once you start to layer the other contexts in the table, the ranking result starts to accumulate each County that is in different States and State Regions.

So, you need to identify and change the context of the calculation to make sure you get the correct overall rank for each County regardless of whatâ€™s selected on the report page.

You can also make a selection using State Regions. If you select Northeast, it shows the proper ranking.

Formula For County Ranking

In this tutorial, two formulas are used to get the County Ranking. This will help you understand the difference in the results generated by using different DAX functions along with RANKX:

Formula #1: Using ALL And ALLSELECTED

The ALL function is the key part of this formula for County Rank:

It manipulates the context within the RANKX formula. It then iterates through the Regions table and creates the ranking result.

In this case, the ALL function is virtually working through every single County regardless of the initial context.

This problem becomes complicated when you start to layer on slicer contexts. By using ALLSELECTED, the ranking will automatically update depending on the selection in the slicer.

You also need to utilize CALCULATE to place additional context to a result or to change the context of the calculation.

The context is adjusted based on the filters within the CALCULATE function.

With this, the ranking will adjust based on the context coming from the State and State Region slicers.

Formula #2: Using ALL And VALUES

Letâ€™s now look at the County (Updated) column.

You want to make sure that this column will continue to show the overall rank regardless of whatâ€™s selected in the slicers. Every County should have a unique number.

This is the formula used:

For this example, there wasnâ€™t a way to decipher a unique County. So, another column was created. The Full County column includes the County, State, and Region.

This gives you a good distinction between Counties. This way, you can now work a virtual table of the Counties that are in the data.

Before doing this, you need to remove the ALL context from the Regions table.

So, any context coming from the Region’s table doesnâ€™t exist at this point in time. With the VALUES function, you can add back the context of the Full County column.

One option is to reference every column using the ALL function. But the better alternative is to get rid of all the context from Regions and only bring back the Full County.

Once the context has been manipulated inside CALCULATE, the RANKX function starts its work from there.

It works through every single County at every row along with their Total Sales, and then ranks them. Thatâ€™s how you get the values of 1 to X under the County (Updated) row.

As a result, filters and slicers wonâ€™t matter and won’t affect the ranking because the formula has been set to ignore them.

Conclusion

RANKX is not an easy function to use. The key is to focus around the nuances to be able to understand the function better.

Hopefully you have learned more about the advanced applications of the DAX function RANKX in Power BI.

You can check out the Mastering DAX Calculations course in Enterprise DNA Online for more discussions on the RANKX function.

All the best,

Sam

Debugging DAX: Tips and Tools for Troubleshooting Your Formulas

One of the main reasons why businesses all over the world have fallen in love with Power BI is because...

Practical Application of TREATAS Function in DAX

A hands-on project focused on using the TREATAS function to manipulate and analyze data in DAX.

MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...