How to Use TOPN in DAX in Power BI: Syntax and Examples

Use the TOPN function in DAX to return the top N rows of a table sorted by one or more columns or expressions. The syntax is TOPN(, , , [Sort_Order]), where you specify how many rows to return and how to sort them.

Syntax

The TOPN function returns the top N rows from a table based on sorting criteria.

• N: Number of rows to return.
• Table: The table to filter.
• OrderBy_Expression: Column or measure to sort by.
• Sort_Order (optional): ASC for ascending or DESC for descending order; default is DESC.

DAX

TOPN(<N>, <Table>, <OrderBy_Expression>, [Sort_Order])

Example

This example returns the top 3 products by total sales from the Products table.

DAX

Top3Products =
TOPN(
    3,
    Products,
    Products[TotalSales],
    DESC
)

Output

Returns a table with the 3 products having the highest TotalSales values.

Common Pitfalls

Common mistakes include:

• Not specifying the sort order, which defaults to descending but can cause confusion.
• Using TOPN on a table without a proper relationship or filter context, leading to unexpected results.
• Trying to use TOPN inside a measure without returning a scalar value.

DAX

/* Wrong: Missing sort order, may cause confusion */
TopProductsWrong = TOPN(5, Products, Products[TotalSales])

/* Right: Explicit sort order for clarity */
TopProductsRight = TOPN(5, Products, Products[TotalSales], DESC)

Quick Reference

Parameter	Description
N	Number of rows to return
Table	Table to filter
OrderBy_Expression	Column or measure to sort by
Sort_Order	Sort direction: ASC or DESC (default DESC)

Syntax

The TOPN function returns the top N rows from a table based on sorting criteria.

• N: Number of rows to return.
• Table: The table to filter.
• OrderBy_Expression: Column or measure to sort by.
• Sort_Order (optional): ASC for ascending or DESC for descending order; default is DESC.

DAX

TOPN(<N>, <Table>, <OrderBy_Expression>, [Sort_Order])

Example

This example returns the top 3 products by total sales from the Products table.

DAX

Top3Products =
TOPN(
    3,
    Products,
    Products[TotalSales],
    DESC
)

Output

Returns a table with the 3 products having the highest TotalSales values.

Common Pitfalls

Common mistakes include:

• Not specifying the sort order, which defaults to descending but can cause confusion.
• Using TOPN on a table without a proper relationship or filter context, leading to unexpected results.
• Trying to use TOPN inside a measure without returning a scalar value.

DAX

/* Wrong: Missing sort order, may cause confusion */
TopProductsWrong = TOPN(5, Products, Products[TotalSales])

/* Right: Explicit sort order for clarity */
TopProductsRight = TOPN(5, Products, Products[TotalSales], DESC)

Quick Reference

Key Takeaways

TOPN returns the top N rows from a table sorted by specified columns or measures.

Always specify the sort order explicitly to avoid confusion.

TOPN returns a table, so use it in calculated tables or with aggregation functions in measures.

Use TOPN with proper filter context to get meaningful results.