TOPN in DAX: Find Top, Least, and Top N Categories

What Is TOPN in DAX?

TOPN is a DAX table function used to return a specified number of
rows from a table expression based on a sorting expression.
You can use it to find the highest or lowest
values depending on the sort direction.

The basic syntax used in these examples is:

TOPN(
    <Number of Rows>,
    <Table Expression>,
    <Order By Expression>,
    <Order Direction>
)

In the queries below, DESC is used to return the highest values,
while ASC is used to return the lowest values.

1. Find Top 10 Transactions

Query

// TOP N

// 1. Find Top 10 Transactions

EVALUATE
    TOPN(
        10, 
        payments,
        [paid],
        DESC
    )
    ORDER BY [Paid] DESC

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of the table expression.
Since TOPN returns a table, it can be used directly with
EVALUATE.

Step 2: TOPN(10, …)
The first argument, 10, tells DAX that we want to retrieve
the top 10 rows from the specified table expression.

Step 3: payments
payments is the table from which the transactions are selected.

Step 4: [paid]
[paid] is the expression used to determine how the rows should
be ranked for the TOPN operation.

Step 5: DESC
DESC means descending order. Therefore, the transactions with
the highest [paid] values are selected.

Step 6: ORDER BY [Paid] DESC
The ORDER BY clause sorts the final result from the highest
paid value to the lowest paid value, making the returned top transactions
easier to read.

What will you get?

The query returns the 10 transactions with the highest paid values,
displayed in descending order.

The values above are illustrative. Your actual result will depend on the data in the payments table.

2. Find Least 10 Transactions

Query

// 2. Find Least 10 Transactions

EVALUATE
    TOPN(
        10, 
        payments,
        [paid],
        ASC
    )
    ORDER BY [Paid] ASC

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the table generated by the
TOPN expression.

Step 2: TOPN(10, …)
The value 10 specifies that we want to return 10 rows.

Step 3: payments
The payments table is used as the source table.

Step 4: [paid]
The [paid] expression is used to determine which transactions
should be selected.

Step 5: ASC
ASC means ascending order. In this query, the transactions
with the lowest [paid] values are selected.

Step 6: ORDER BY [Paid] ASC
The final result is displayed from the lowest paid value to the highest paid value.

What will you get?

The query returns the 10 transactions with the lowest paid values,
displayed in ascending order.

The values above are illustrative. Your actual result will depend on the data in the payments table.

3. Find the Top 3 Courses

Query

// 3. Top 3 Courses

EVALUATE
    TOPN(
        3,
        SUMMARIZE(
            payments,
            courses_natural[Name],
            "Total Payments", SUM( payments[Paid])
        ),
        [Total Payments],
        DESC
    )
    ORDER BY [Total Payments] DESC

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the table generated by the complete
TOPN expression.

Step 2: TOPN(3, …)
The first argument is 3, so the query is designed to return
the top 3 courses.

Step 3: SUMMARIZE
Instead of applying TOPN directly to the transaction-level
payments table, the query first creates a summarized table.

Step 4: courses_natural[Name]
The course name is used as the grouping column. This produces one summarized
row for each course.

Step 5: “Total Payments”
A calculated column named Total Payments is created in the
summarized result.

Step 6: SUM(payments[Paid])
The Paid values are aggregated for each course to calculate
its Total Payments.

Step 7: [Total Payments]
TOPN uses the calculated Total Payments
value to determine which courses belong in the top 3.

Step 8: DESC
The courses are ranked in descending order, so the courses with the highest
total payments are selected.

Step 9: ORDER BY [Total Payments] DESC
Finally, the selected courses are displayed from the highest total payment
to the lowest total payment.

What will you get?

The result contains the top 3 courses based on their total payments.

The course names and values above are illustrative. Your actual result will depend on the data in the payments and courses_natural tables.

Understanding the Three TOPN Queries

Important Points About TOPN

1. The first argument controls the number of rows

In these examples, 10 returns the top or least 10 transactions,
while 3 returns the top 3 courses.

2. DESC finds the highest values

When DESC is used as the order direction, the rows with the
highest values of the specified expression are selected.

3. ASC finds the lowest values

When ASC is used, the rows with the lowest values are selected.

4. TOPN works with summarized data

The third example demonstrates an important analytical pattern:
first summarize the data by course, calculate Total Payments,
and then use TOPN to identify the highest-performing courses.

5. ORDER BY controls the displayed result order

TOPN determines which rows are returned, while the
ORDER BY clause in these queries controls how those returned
rows are displayed in the final result.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist