DAX

Dax Function: TOPN

Q: Can I use TOPN to find the bottom N rows?

No, but you can use the BOTTOMN function for retrieving the lowest-ranked rows.

Q: Is it possible to dynamically control N in TOPN?

Yes, use a slicer with SELECTEDVALUE to dynamically adjust the value of N.

Q: Can I use multiple sorting expressions with TOPN?

No, TOPN supports only one sorting expression at a time.

Category: Table Manipulation Functions

The TOPN function in Power BI is a DAX function used to return the top N rows of a table based on a specified ranking criteria. It enables users to extract the highest or lowest-ranked items in a dataset.

Purpose

To rank and filter data based on specified measures or expressions.
Extract a subset of a dataset by selecting the top or bottom N rows.
Useful for analysis involving leaders (e.g., top-selling products, highest-performing employees).

Type of Calculations

Performs ranking and filtering on tables.
Sorts data based on one or more expressions.
Returns a table containing the top N rows matching the criteria.

Practical Use Cases

Sales Analysis: Identify top-performing products or regions.
Performance Tracking: Highlight employees with the highest productivity or efficiency.
Dynamic Filtering: Pair with slicers to adjust N dynamically (e.g., show top 5, 10, or 20).


TOPN(<n_value>, <table>, <orderby_expression>, [<order>])</order></orderby_expression></table></n_value>

Parameter	Type	Description
N_Value	Scalar	The number of rows to return. Must be a positive integer or an expression resolving to a positive integer.
Table	Table	The table from which the top rows are to be extracted.
OrderBy_Expression	Expression	The expression used to evaluate and rank rows.
Order (optional)	Text	Specifies sorting order: `"ASC"` for ascending or `"DESC"` for descending. Default is `"DESC"`.

How Does TOPN Dax Works

Rank Rows: Sort rows in the input table using the OrderBy_Expression.
Select Rows: Extract the top N rows based on the specified order (ASC or DESC).
Return a Table: Output the resulting subset as a table.

Logical Principle

Input Example:

TOPN(3, Sales, Sales[Revenue], "DESC")

Output:

Product	Revenue
A	500
B	400
C	300

Returns the top 3 products based on descending revenue.

What Does It Return?

Type: Table.
Content: A subset of rows from the input table, limited to the top N rows based on the sorting expression.

When Should We Use It?

To focus on the most significant contributors in your dataset.
For reports or dashboards that require dynamic leaderboards.
When you need to simplify large datasets by extracting top-performing elements.

Examples

Basic Usage :

Retrieve Top 5 Customers by Sales:


TOPN(5, Sales, Sales[TotalSales], "DESC")

Result: A table with the top 5 customers based on total sales.

Combine with Filters

Top 3 Products in a Specific Region:


CALCULATETABLE(
TOPN(3, Products, Products[Revenue], "DESC"),
Products[Region] = "North"
)

Result: The top 3 products by revenue in the “North” region.

Advanced Usage

Dynamic Top N Using a Slicer:


TOPN(
SELECTEDVALUE(SlicerTable[TopN_Value]),
Sales,
Sales[TotalSales],
"DESC"
)

Result: Dynamically returns the top N rows based on a slicer value.

Tips and Tricks

Combine with CALCULATETABLE for contextual filtering.
Use with SELECTEDVALUE or parameters for dynamic ranking.
Ensure N_Value is a positive integer; negative or non-numeric values cause errors.
Avoid using on large datasets without filtering, as it can affect performance.

Performance Impact of TOPN DAX Function:

Optimizations: Use pre-filtered tables to reduce the computation load.
Large Datasets: Apply TOPN on aggregated datasets rather than raw data.

Related Functions You Might Need

Function	Description
`RANKX`	Ranks rows based on a specified expression.
`FILTER`	Filters rows of a table based on a condition.
`CALCULATETABLE`	Applies a filter context to a table expression.
`BOTTOMN`	Similar to `TOPN` but retrieves the bottom N rows.

Want to Learn More?
For more information, check out the official Microsoft documentation for TOPN You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the TOPN function do in Power BI?

It retrieves the top N rows from a table based on a specified sorting expression.

2. Can I use TOPN to find the bottom N rows?

No, but you can use the BOTTOMN function for retrieving the lowest-ranked rows.

3. Is it possible to dynamically control N in TOPN?

Yes, use a slicer with SELECTEDVALUE to dynamically adjust the value of N.

4. What happens if two rows have the same rank in TOPN?

If a tie occurs, all tied rows will be included if they fall within the top N ranks.

5. Can I use multiple sorting expressions with TOPN?

No, TOPN supports only one sorting expression at a time.

Dax Function: Table Constructor

Dax Function: TREATAS