TOPN and RANKX Functions

TOPN Function

The TOPN function in DAX is used to return a specified number of 'top' rows based on a defined expression. It will return a table that contains top N rows, where N, a numeric expression, defines the number of top rows to return.

Here is the general syntax for TOPN:

TOPN(<N_Value>, <Table_Name>, <Expression>, [<Order_By_Expression> [ASC|DESC]], …)

For example, to get top 5 Products by Sales:

Top5 = 
TOPN(
    5,
    'Product',
    CALCULATE(
        SUM('Product'[Sales])
    )
)

In this example, the TOPN function returns the top 5 products based on the total of sales.

RANKX Function

On the other hand, the RANKX function in DAX is used to assign a ranking to each row in a table based on a given expression. The ranking is performed within the context of the current filters applied to the data.

Here is the general syntax for RANKX:

RANKX( <Table_Name>,<Expression>[,<Value>[,<Order>[,<Ties>]]])

For example, to rank the products by Sales:

ProductRank = 
RANKX(
    ALL('Product'),
    CALCULATE(
        SUM('Product'[Sales])
    )
)

In this example, the RANKX function returns a ranking for each of the products based on the total of sales.

Summary

1. TOPN returns a table with the top N rows based on an expression.
2. RANKX assigns a ranking/order number to each row in a table based on an expression.

The major difference between these two DAX functions is, while TOPN returns a subset of data that fits the "top N" criteria, RANKX assigns a rank to each row of data. RANKX function is thus useful to understand the position of a specific row across the entire data, whereas TOPN function is useful when you want to simply filter out the top performing data points.