RANKX function (DAX) in Power BI: An Easy Guide 101

Microsoft’s Power BI is a technology-driven Business Intelligence tool for analyzing and visualizing raw data in order to present actionable data. It brings together Business Analytics, Data Visualization, and best practices to assist organizations in making data-driven decisions. Because of the capabilities of the Power BI platform, Gartner named Microsoft the Leader in the “2019 Gartner Magic Quadrant for Analytics and Business Intelligence Platform” in February 2019.

In DAX, the RANKX Power BI function is a Scalar function, which means it only returns one value. It can’t be used to make a table on its own; it needs to be combined with tabular functions. The RANKX Power BI also helps compute ranking over a table.

This article talks about the RANKX Power BI Function, what it is and how you can use it with a few examples.

Table Of Contents

• What is Power BI?
  • Key Features of Power BI 
• What is RANKX Power BI Function?
• Understanding RANKX Power BI Function
  • Understanding RANKX Power BI Function: Example 1
  • Understanding RANKX Power BI Function: Example 2
• Conclusion

What is Power BI?

Power BI is a proprietary Business Intelligence tool designed for seamless Data Analytics and Data Visualization. It is a part of the Microsoft Power Platform. Power BI is one of the common tools used by organizations for analyzing their business data construct reports. 

It comes with a collection of various in-built software services, apps, and connectors that deeply integrates with data sources to deliver immersive visuals, interactive reports, and generate insights. Power BI uses its advanced charts, graphs, and other visuals included with Machine Learning to easily extract valuable information out of data.

Power BI can also read data from XML files, CSV files, JSON format files, and even web pages, then convert raw data into interactive insights. It is available for Desktop, mobile, and on-premise servers. Users can create and share their reports with other Power BI users within the organization or partner companies. 

Key Features of Power BI

Some of the main features of Power BI are listed below:

• Supports API Integrations: Power BI allows developers to easily integrate with other applications and embed dashboards into other software using sample codes and APIs.
• Custom Visualization: Power BI offers custom visualization libraries support that allows users to visualize complex data with ease.
• AI Support: Users can easily perform Data Analytics using Artificial Intelligence. With the built-in AI support, users can prepare data, build Machine Learning models, and gain insights.
• Modeling View: With the help of Modeling View, Power BI users can slice and divide the complex data into simpler ones that help in better understanding the data, separate diagrams, and multi-select objects.
• Easy Sharing: Power BI makes it easier for users to easily share their reports within teams, or organizations ensuring full data protection.
• Hybrid Development: Power BI easily integrates with many 3rd party connectors, applications, and services widely used by organizations that allow users to connect to various data sources.

To know more about Power BI, click here.

What is RANKX Power BI Function?

Data Analysis Expressions (DAX) is a programming language that can be used to create calculated columns, measures, and custom tables in Microsoft Power BI. It is a set of functions, operators, and constants that can be used to calculate and return one or more values using a formula, or expression. The DAX language has a function that returns the ranking of a sorted element based on a given expression. The RANKX Power BI is one such function; it is both a scalar and an iterator. The RANKX Power BI function is also a robust sorting tool.

The RANKX Power BI returns the ranking of a number in a list of numbers for each row in the table argument. 

The Syntax of the RANKX Power BI function is given below:

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])  

• The parameters are explained as follows:
  • Table: Any DAX expression that returns a table of data to evaluate over.
  • Expression: Any DAX expression with a single scalar value as the result. To generate all possible rankings values, the expression is evaluated for each row of the table.
  • Value: This is an optional parameter. Any DAX expression that returns a single scalar value with a rank needs to be determined. When the value parameter is not specified, the current row’s expression value is used instead.
  • Order: This is an optional parameter. A value that specifies whether the value should be ranked from low to high or from high to low:

value	alternate value	Description
0 (zero)	FALSE	Ranks in descending order of values of expression. If the value is equal to the highest number in expression then RANKX returns 1. This is the default value when the order parameter is omitted.
1(one)	TRUE	Ranks in ascending order of expression. If the value is equal to the lowest number in expression then RANKX returns 1.

• Ties: This is an optional parameter. When there are ties, an enumeration that defines how to determine ranking is used.

enumeration	Description
Skip	The next rank value, after a tie, is the rank value of the tie plus the count of tied values. For example, if five (5) values are tied with a rank of 11 then the next value will receive a rank of 16 (11 + 5). This is the default value when the ties parameter is omitted.
Dense	The next rank value, after a tie, is the next rank value. For example, if five (5) values are tied with a rank of 11 then the next value will receive a rank of 12.

• Return Value: For all rows of table numbers, the rank number of values among all possible values of expression is evaluated.
• Some things to consider while using the RANKX Power BI function are:
  • When an expression or value evaluates to BLANK, it is treated as a 0 (zero) for all number expressions and as an empty string for all text expressions.
  • If the value is not among the expression’s possible values, RANKX temporarily adds value to the expression’s values before re-evaluating RANKX to determine the proper rank of value.
  • Optional arguments can be skipped by using an empty comma (,) in the argument list, for example, RANKX (Inventory, [InventoryCost], “Dense”)
  • When used in calculated columns or row-level security (RLS) rules, this function is not supported when using DirectQuery mode.
• An example of the RANKX Power BI function is given below:

= RANKX(ALL(Products), SUMX(RELATEDTABLE(InternetSales), [SalesAmount])) 

The Sales ranking for each product in the Internet Channel is calculated by this column in the Products table.

Understanding RANKX Power BI Function

Understanding RANKX Power BI Function: Example 1

A simple example of the RANKX Power BI function is:

• You will begin with the most fundamental RANKX Power BI example in a calculated column. You will want to rank your Total Sales for all of the rows in our table here. Enter the following formula:

RANX Total Sales =
RANKX(
‘Sales Table’,
‘Sales Table'[Total Sales]
)

This gives you the expected result. The new column ranked Total Sales from the highest to the lowest, with the highest number having a value of 1. You’ll notice the ties here as well; take a closer look at rank 4. You’ll notice that sales for customers 9 and 3 were $243 each, with a rank of 4, and then it skipped 5 and ranked Total Sales for customer 6 with a rank of 6. This is a good starting point for understanding how the default parameters work.

• You can now test the behavior of not skipping rank values by passing DENSE for the ties parameter.

RANX Total Sales DENSE =
RANKX(
‘Sales Table’,
‘Sales Table'[Total Sales],
,
,
Dense
)

It’s critical to note what the difference is right now. There are no ranking values skipped by the DENSE input. Take the rank of 4 again, and you’ll notice that Customer 6 now has a rank value of 5 instead of 6 as in the default or SKIP state of RANKX Power BI. As a result, it’s crucial to decide which method you want to use so that your ranking values make sense.

• Rearrange the order and assign the lowest number of Total Sales a 1:

RANX Total Sales ASC =
RANKX(
‘Sales Table’,
‘Sales Table'[Total Sales],
,
ASC
)

• As you can see, adding an ASC order value to the RANKX Power BI function causes this function to start with the lowest value and assign it a RANKX of 1.

Understanding RANKX Power BI Function: Example 2

• When applied to a column with a specific sort order in the data model, the RANKX Power BI function may behave in an unexpected way. The behavior of RANKX Power BI function is explained by the following example.

Take a look at the table below for an example:

• You can use the Value numeric column in the Name column to perform a Sum Value ranking.

[Sum Value] :=
SUM ( Test[Value] )
 
[Rank Name Simple] :=
IF (
    HASONEVALUE ( Test[Name] ),
    RANKX ( ALL ( Test[Name] ), [Sum Value] )
)

• Here’s an example of why you should use a measure instead of a calculated column: You might want to use the current filters to display the top three names (you might filter the State column, for example). You can get the desired result using such a measure by taking advantage of the fact that tables and matrixes in Power BI hide the rows that return blank in all displayed measures:

[Top 3 Cases] :=
IF (
    [Rank Name Simple] <= 3,
    [Sum Value]
)

You get the following result:

• You notice, however, that the sort order isn’t what you expected. In this simple case, you could simply apply the proper sort order to the report’s column Top 3 Cases, but in more complex cases, the requirement may be different, and the sort order should be defined by another table column. In this case, you’d like to sort the Name column in ascending order by using the Value column from the same table. As a result, as shown in the dialog box below, you apply the Sort By Column to the Name column.

• At this point, your previous example is no longer valid, as it now displays all of the names rather than just the top three.

• You also show the Rank Name Simple measure to help you understand why this is happening.

• The RANKX Power BI function behavior appears to be broken when the Sort By Column setting is used. It appears to be a bug, but it isn’t. The reason for this is that Power BI creates a query like this for the previous report:

EVALUATE
TOPN (
    502,
    SUMMARIZECOLUMNS (
        ROLLUPADDISSUBTOTAL (
            ROLLUPGROUP ( 'Test'[Name], 'Test'[Value] ), "IsGrandTotalRowTotal"
        ),
        "Top_3_Cases", 'Test'[Top 3 Cases]
    ),
    [IsGrandTotalRowTotal], 0,
    'Test'[Value], 1,
    'Test'[Name], 1
)
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'Test'[Value],
    'Test'[Name]

As you can see, the Rank Name Simple measure is calculated in a row context created by iterating two columns from the table Test: Name and Value. This happens regardless of the table’s column settings. In fact, before the measures, you only included the Name column in the report.

When you enable the Sort By Column setting, Power BI includes the corresponding sort column in the same DAX query every time you include a column in a report. For most aggregations, this isn’t an issue because the two columns should have the same granularity. When a context transition (the measure [Sum Value]) occurs during an iterator (the RANKX Power BI function), the presence of the additional sort column (Value) in the row context that evaluates the measure generates a filter context that includes a filter over the same Value item for all the rows iterated by RANKX Power BI function.

• When applying RANKX Power BI function to a single column, you can avoid this problem by including the sort column. For instance, you could write:

[Rank Name] :=
IF (
    HASONEVALUE ( Test[Name] ),
    RANKX ( ALL ( Test[Name], Test[Value] ), [Sum Value] )
)

• This ensures that the context transition produced by Sum Value in RANKX will override the filter context in the query generated by Power BI for both the Name and Value columns. This strategy yields the expected outcome:

• If we remove all of the unnecessary measures from the report, we can now see the top three names.

• If you’re using the RANKX Power BI function, keep in mind that sorting by column in the data model might break an existing measure. To generate the correct measure in DAX, you should know that there is a sort by column condition. This issue should not affect iterators over physical tables, but the table granularity may not be appropriate for your calculations, and the context transition when iterating a table may be slower.

Conclusion

This article gives a detailed description of the RANKX Power BI function and how it is used in different examples. When calculating rank as a value in a Power BI visual, the RANKX Power BI scalar DAX function can be extremely useful.

Visit our Website to Explore Hevo

Power BI is a great tool for performing Data Analytics and Visualization for your business data. However, at times, you need to transfer this data from multiple sources to your PowerBI account for analysis. Building an in-house solution for this process could be an expensive and time-consuming task. Hevo Data, on the other hand, offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc.

This platform allows you to transfer data from 100+ sources to BI tools like Power BI, and Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.