TOPN

Article
04/25/2024

Applies to: Calculated column Calculated table Measure Visual calculation

Returns the top N rows of the specified table.

Syntax

TOPN(<N_Value>, <Table>, <OrderBy_Expression>, [<Order>[, <OrderBy_Expression>, [<Order>]]…])

Parameters

Parameter	Definition
N_Value	The number of rows to return. Any DAX expression that returns a scalar value, where the expression is to be evaluated multiple times (for each row/context). See Remarks to better understand when the number of rows returned could be larger than n_value.
Table	Any DAX expression that returns a table of data from where to extract the top 'n' rows. See Remarks to better understand when an empty table is returned.
OrderBy_Expression	(Optional) Any DAX expression where the result value is used to sort the table and evaluated for each row of table.
Order	(Optional) A value that specifies how to sort OrderBy_Expression values: - 0 (zero) or FALSE. Sorts in descending order of values of Order. Default when Order parameter is omitted. - 1 or TRUE. Ranks in ascending order of OrderBy.

Return value

A table with the top N rows of Table or an empty table if N_Value is 0 (zero) or less. Rows are not sorted in any particular order.

Remarks

If there is a tie, in Order_By values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows.
If N_Value is 0 (zero) or less, TOPN returns an empty table.
TOPN does not guarantee any sort order for the results.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

The following measure formula returns the top 10 sold products by sales amount.

= SUMX(
        TOPN(
            10, 
            SUMMARIZE(
                    InternetSales, 
                    InternetSales[ProductKey], 
                    "TotalSales", SUM(InternetSales[SalesAmount])
            ),
            [TotalSales], DESC
        ),
        [TotalSales]
)

Share via