TOPNSKIP
n rows of a table after skipping a number of them, optionally in an order: for paging through rows.
Group: Table manipulation. Compatible with DAX.
Syntax
TOPNSKIP ( <N_Value>, <Skip_Rows_Value>, <Table> [, <OrderBy_Expression> [, <Order>]] … )| Parameter | Type | Description |
|---|---|---|
N_Value | Number | How many rows to return. |
Skip_Rows_Value | Number | How many rows to pass over first: 0 to start at the first row. |
Table | Table | The rows to page through. |
OrderBy_Expression | Expression optional, repeatable, evaluated for each row | A value worked out for each row that sets the order. Without it, the rows come as the table holds them. |
Order | Keyword optional, repeatable | DESC (largest first, when left out) or ASC, for the order value before it; 0 or FALSE, 1 or TRUE say the same. Any other value is an error. |
Returns
Table. N rows of the table, after the rows skipped, in the order given. They are not sorted.
Remarks
- It is made for paging through a large table: the first hundred rows, the next hundred and so on, with a skip of 0, 100, 200.
- Several order values may follow one another, each with its own direction: the second decides between rows the first leaves tied.
- Give an order with no ties where two pages meet, so each row lands on exactly one page.
- A skip past the last row gives an empty table.
- Sort the rows with ORDER BY when they are shown: TOPNSKIP picks them without sorting them.
Compared with DAX
Power BI Desktop skips rows only over a table of the model ordered by one value: with a skip over a variable's rows, VALUES, or two order values, it refuses the query. OpenBI sorts the rows and skips them as asked in every case, so the second page below answers in OpenBI only. With a skip of 0, both answer alike.
Examples
The first page, largest or smallest first
EVALUATE
VAR Shops =
DATATABLE (
"Shop", STRING, "Liters", INTEGER,
{ { "A", 50 }, { "B", 80 }, { "C", 65 }, { "D", 20 }, { "E", 35 } }
)
RETURN
ROW (
"Largest first", CONCATENATEX ( TOPNSKIP ( 2, 0, Shops, [Liters] ), [Shop], ", ", [Liters], DESC ),
"Smallest first", CONCATENATEX ( TOPNSKIP ( 2, 0, Shops, [Liters], ASC ), [Shop], ", ", [Liters], ASC )
)| Largest first | Smallest first |
|---|---|
| B, C | D, E |
The second page
An OpenBI answer: Power BI Desktop refuses a skip over a variable's rows.
EVALUATE
VAR Shops =
DATATABLE (
"Shop", STRING, "Liters", INTEGER,
{ { "A", 50 }, { "B", 80 }, { "C", 65 }, { "D", 20 }, { "E", 35 } }
)
RETURN
TOPNSKIP ( 2, 2, Shops, [Liters] )
ORDER BY [Liters] DESC| Shop | Liters |
|---|---|
| A | 50 |
| E | 35 |
Rows 4 to 6 of a model table
The fourth to sixth largest rows of Sales by liters.
EVALUATE
TOPNSKIP ( 3, 3, Sales, Sales[Liters], DESC )
ORDER BY Sales[Liters] DESC| Sales[Month] | Sales[Shop] | Sales[Product] | Sales[Liters] |
|---|---|---|---|
| 2026-07-01 | Shop Majorstuen | Pils | 5732 |
| 2025-07-01 | Shop Nydalen | Pils | 5671 |
| 2026-07-01 | Shop St. Hanshaugen | Pils | 5577 |