DAX Aggregation - TOPN function

Description

Returns the top specified number of rows of the table.

Syntax

TOPN (<n_value>, <table>, <orderBy_expression>, [<order>],
   [<orderBy_expression>, [<order>]] …)

Parameters

Sr.No. Parameter & Description
1

n_value

The number of rows to return.

It is any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).
2

table

Any DAX expression that returns a table of data from where to extract the top n_value number of rows.
3

orderBy_expression

Any DAX expression where the result value is used to sort the table and it is evaluated for each row of table.
4

order

Optional.

A value that specifies how to sort orderBy_expression values, ascending or descending −

0 (zero) or FALSE − Sorts in a descending order of values of orderBy_expression.

1 or TRUE − Sorts in an ascending order of orderBy_expression. If omitted, default is 0.

Return Value

  • Returns a table with the top n_value number of rows of table, if n_value > 0.
  • Returns an empty table, if n_value <= 0.

Rows are not necessarily sorted in any particular order.

Remarks

  • If there is a tie, in orderBy_expression values, at the Nth row of the table, then all tied rows are returned. The function might return more than n_value number of rows.

  • TOPN does not guarantee any sort order for the results.

Example

= SUMX (TOPN (15,Sales,Sales[Salesperson],ASC),Sales[Sales Amount])
dax_functions_aggregation.htm
Advertisements