Daily DAX : Day 345 TOPNSKIP

TOPNSKIP DAX Function in Power BI

The TOPNSKIP DAX function retrieves a specified number of rows from a table, skipping a defined number of rows, based on a sort order. It’s useful for paginated reports or scenarios requiring a subset of ranked data.

Syntax

TOPNSKIP(n_value, skip_value, table, orderBy_expression [, order [, ...]])
    

• n_value: Number of rows to return.
• skip_value: Number of rows to skip.
• table: The table to query.
• orderBy_expression: Column or expression to sort by.
• order: (Optional) ASC or DESC (default is ASC).

Use Case

TOPNSKIP is ideal for scenarios like:

• Paginated Reports: Display a fixed number of rows per page (e.g., top 10 products, skipping the first 20 for page 3).
• Top Performers: Retrieve top N customers by sales, skipping initial rows for ranking analysis.
• Data Sampling: Extract a subset of sorted data for performance or visualization purposes.

Example

Retrieve the 10 highest sales from the Sales table, skipping the first 20, sorted by SalesAmount in descending order:

TopSales = TOPNSKIP(10, 20, Sales, Sales[SalesAmount], DESC)
    

This returns rows 21–30 of the Sales table, ordered by SalesAmount from highest to lowest.

Notes

• Requires a valid table and sort expression.
• Performance may be impacted with large datasets.
• Useful in combination with visuals or measures for dynamic reporting.