TOPN
The first n rows of a table in a given order; rows tied with the last one all come along.
Group: Table manipulation. Compatible with DAX.
Syntax
TOPN ( <N_Value>, <Table> [, <OrderBy_Expression> [, <Order>]] … )| Parameter | Type | Description |
|---|---|---|
N_Value | Number | How many rows to return. |
Table | Table | The rows to choose from. |
OrderBy_Expression | Expression optional, repeatable, evaluated for each row | A value worked out for each row that sets the order. Without it, the first rows are taken as the table holds them. |
Order | Keyword optional, repeatable | DESC (largest first, when left out) or ASC, for the order value before it; 0 and 1 say the same. |
Returns
Table. The first rows of the table in the order given, with all its columns. They are not sorted.
Remarks
- Rows tied with the last one all come back, so the result can have more than N rows: two shops sharing the top value both appear in a TOPN of 1. A second order value, such as a name, breaks the tie.
- TOPN picks rows; it does not sort them. Sort with ORDER BY in a query, or with the order arguments of
CONCATENATEXand the like. - A blank counts as zero: from the largest down, it comes after the positive values and before the negative ones.
- An N of 0 or less gives an empty table; an N above the number of rows gives them all.
- With a measure as the order, each row is evaluated as a filter:
TOPN ( 2, VALUES ( Products[Product] ), [Liters sold] )is the two best-selling products.
Examples
Ties at the boundary
B and C share the top value, so a TOPN of 1 returns both.
EVALUATE
VAR Shops =
DATATABLE (
"Shop", STRING, "Liters", INTEGER,
{ { "A", 50 }, { "B", 80 }, { "C", 80 }, { "D", 20 } }
)
RETURN
TOPN ( 1, Shops, [Liters] )
ORDER BY [Shop]| Shop | Liters |
|---|---|
| B | 80 |
| C | 80 |
A second order value breaks the tie
EVALUATE
VAR Shops =
DATATABLE (
"Shop", STRING, "Liters", INTEGER,
{ { "A", 50 }, { "B", 80 }, { "C", 80 }, { "D", 20 } }
)
RETURN
TOPN ( 1, Shops, [Liters], DESC, [Shop], ASC )
ORDER BY [Shop]| Shop | Liters |
|---|---|
| B | 80 |
The two best-selling products
EVALUATE
ADDCOLUMNS (
TOPN ( 2, VALUES ( Products[Product] ), [Liters sold] ),
"Liters", [Liters sold]
)
ORDER BY [Liters] DESC| Products[Product] | Liters |
|---|---|
| Pils | 1004326 |
| IPA | 331884 |