DAX Aggregation - TOPN function


Advertisements

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

Useful Video Courses


Video

Mastering DAX and Data Models in Power BI Desktop

53 Lectures 5.5 hours

Abhay Gadiya

Video

Mastering DAX Studio

Featured

24 Lectures 2 hours

Randy Minder

Video

DAX / Power BI - Customer and Sales Analysis Deep Dive

26 Lectures 4.5 hours

Randy Minder

Advertisements