Building a Measure with the DAX RankX Function

Aug 15, 2024

—

Using the RANK() Function in DAX

To demonstrate the use of the DAX function, we’ll start by creating a simple sales table with the sales amount and the year. Then we’ll add additional columns to create examples of using the RANK function.

In the first example (SimpleSalesRank), we’ll just create a simple ranking. The default function ranks the Sales column from the least amount to the highest amount and you’ll notice the blank sales value is included as rank one. Note, we can order by more than one column if partitioning by more than one column.

SimpleSalesRank = RANK(ORDERBY(SalesRank[Sales]))

The first thing we can do is move the blank value to the end of the ranking, using the LAST parameter.

SimpleSalesRankBLANKLast = RANK(ORDERBY(SalesRank[Sales]), LAST)

Then we can rank the sales values from the highest first, while still retaining blank in the last position.

SimpleSalesRankDESC0 = RANK(ORDERBY(SalesRank[Sales], DESC), LAST)

We can then partition the ranking by using the Year column.

SimpleSalesRankPARTITION = RANK(DENSE, ORDERBY(SalesRank[Sales], DESC),  LAST, PARTITIONBY(SalesRank[Year]))

Using RANKX() function in DAX

In the next example, we create a similar Sales table with a Sales column and a year column. We can then use the RANK functions to create our rankings.

First, we’ll create a simple ranking as before, but with RANKX(). Included here is the function syntax in the comments.
As you can see from the table above, the RANKX function defaults to sorting the highest sales value first, whereas the RANK function sorts it last. The RANKX function also defaults to putting the blank last, whereas the RANK function ordered it first.

RankXFunction = 
--RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])  
RANKX(SalesRankX2, SalesRankX2[Sales])

We can reverse the order with the following code, but you’ll notice we don’t use the ORDERBY clause in the code:

RankXFunctionASC = RANKX(SalesRankX2, SalesRankX2[Sales],,  ASC)

We can also apply the DENSE clause for the ties, as the default is to SKIP the ranking when there have been equal values, for example, there are 2 sales of value 94, which are both ranked 6, as the default treatment of ties is set to SKIP, the next rank value jumps to 8. With the DENSE clause, the next rank does not jump.

RankXFunctionDENSE = 
--RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])  
--note: defaul ties is SKIP
RANKX(SalesRankX2, SalesRankX2[Sales], ,  ASC, DENSE)

In the next example we use RANKX with RELATEDTABLE(). We start of by creating a sample of the DimCustomer table, joining it to the FactOnline Sales table and then adding a ranking table to the new customer table for total sales. We then check the ranking by adding a totalsales

Step 1: Create a sample customer table. In the core below, we create a simple selection of CustomerKey, FirstName, LastName and then filter the table by the first 10 customers by customerkey. So this will give us a table of the first 10 customers in the DimCustomer table. These data come from the Contoso Retail Data Warehouse.

CustomerSimple = CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName]
    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

The table below is created (first 3 columns), then we add the SalesRank and CustomerTotalSalesCheck to demonstrate the use of the RANKX function with RELATEDTABLE function

The code for ranking for sales is below. Then we add the CustoemrTotalSalesCheck to confirm that our ranking is working correctly.
As you can see it is. The first rank of 1 is allocated to total sales of £3,932. The last value is blank ranked 10. You will notice there is no rank 8, so the default tie is to SKIP rank when then is a tie. We can change this by adding the DENSE clause

SalesRank = RANKX(CustomerSimple, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]))

Add the the CustomerTotalSales Check column:

CustomerTotalSalesCheck = SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount])

Next, we add the DENSE so the ties don’t jump (optional)

SalesRankDENSE = RANKX(CustomerSimple, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense)

Creating a Measure using RANKX()
Now we have some good examples of using the RANK and RANKX function we can use them in a measure, which creates a temporary table in memory.

To create the measure we can use the DAX Editor in Power BI, DAX Tabular Editor, or DAX Studio. My preference is the DAX Tabular editor, but the code will work in all three, allowing us to see the data as we build the measure. If you just try and build the measure, you can’t be sure what’s going on unless you build the table directly in Power BI, but they are slower to run.

Here we borrow the code from the previous example creating a physical table, but ensure the table is set to DimCustomer in the Rank function.

DEFINE
VAR CustomerSample = 
CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName], 
		"Rank", RANKX(DimCustomer, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense) ,
	    "TotalSalesCheck",  SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]) 
		    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

EVALUATE
CustomerSample

Here is the output of the code in DAX Query View (inside Power BI, I haven’t used tabular editor this time).

As you can see the top rank is 3932 as before.

I can now for example filter the rankings table, in this example, I filter greater than 1 and less than 4

DEFINE
VAR CustomerSample = 
CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName], 
		"RankNumber", RANKX(DimCustomer, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense) ,
	    "TotalSalesCheck",  SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]) 
		    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

VAR TopThreeRankSales = FILTER(CustomerSample, AND([RankNumber] > 1, [RankNumber]< 4))

EVALUATE
TopThreeRankSales

This gives me a new table:

Next, we want to get the sum of the 2nd and 3rd-ranking sales, we can do this by using the GROUPBY function, but without grouping on any columns.

DEFINE
VAR CustomerSample = 
CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName], 
		"RankNumber", RANKX(DimCustomer, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense) ,
	    "TotalSalesCheck",  SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]) 
		    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

VAR TopThreeRankSales = FILTER(CustomerSample, AND([RankNumber] > 1, [RankNumber]< 4))
VAR SalesOfSecondAndThird = GROUPBY(TopThreeRankSales, "SalesOfSecondAndThird", SUMX(CURRENTGROUP(), [TotalSalesCheck]))

EVALUATE
SalesOfSecondAndThird

This gives the following output:

We should now be able to use the DAX in a measure, so we transfer the DAX code into a Measure as follows:
The code using DEFINE and EVALUATE is a DAX query in the DAX query editor. When we create a measure we are creating a DAX expression. DAX expressions are converted to DAX queries when they are evaluated. Here is the code for creating the measure below.

_SecondAndThirdTotalSales = 
VAR CustomerSample = 
CALCULATETABLE(
    SELECTCOLUMNS(
        DimCustomer,
        "CustomerKey", DimCustomer[CustomerKey],
        "FirstName", DimCustomer[FirstName],
        "LastName", DimCustomer[LastName], 
		"RankNumber", RANKX(DimCustomer, SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]),,,Dense) ,
	    "TotalSalesCheck",  SUMX(RELATEDTABLE(FactOnlineSales), FactOnlineSales[SalesAmount]) 
		    ),
    TOPN( 10, DimCustomer, DimCustomer[CustomerKey], ASC )
)

VAR TopThreeRankSales = FILTER(CustomerSample, AND([RankNumber] > 1, [RankNumber]< 4))
VAR SalesOfSecondAndThird = GROUPBY(TopThreeRankSales, "SalesOfSecondAndThird", SUMX(CURRENTGROUP(), [TotalSalesCheck]))

RETURN
SalesOfSecondAndThird

We can then use the measure in a visualization as follows: