TOPN DAX Function (Table manipulation)
Returns a given number of top rows according to a specified expression.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| N_Value |
The number of rows to return. |
|
|
Table Iterator |
An expression that defines the table from which rows are to be returned. |
|
|
OrderBy_Expression Row Context |
Optional Repeatable |
Expression to be used for sorting the table. |
| Order | Optional Repeatable |
The order to be applied. 0/FALSE/DESC – descending; 1/TRUE/ASC – ascending. |
Return values
A table with the top N_value rows of Table or an empty table if N_value is 0 (zero) or less.
Remarks
If there is a tie, in OrderBy_Expression values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows.
If N_Value is 0 (zero) or less than 0, then TOPN returns an empty table.
TOPN does not guarantee any sort order for the results.
» 1 related function
Examples
-- TOPN retrieves the top N items from a table after sorting
-- them by the result of the third argument.
-- Multiple sorting criteria can be provided in further parameters.
EVALUATE
TOPN (
3,
ADDCOLUMNS (
VALUES ( 'Product'[Product Name] ),
"@Sales Amount", [Sales Amount]
),
[@Sales Amount],
DESC
)
ORDER BY [@Sales Amount] DESC
| Product Name | @Sales Amount |
|---|---|
| Adventure Works 26″ 720p LCD HDTV M140 Silver | 1,303,983.46 |
| A. Datum SLR Camera X137 Grey | 725,840.28 |
| Contoso Telephoto Conversion Lens X400 Silver | 683,779.95 |
-- TOPN might return more than the requested rows in presence of ties.
EVALUATE
TOPN (
3,
ADDCOLUMNS (
VALUES ( 'Product'[Product Name] ),
"@Sales Amount", MROUND ( [Sales Amount], 500000 )
),
[@Sales Amount],
DESC
)
ORDER BY [@Sales Amount] DESC
-- Multiple sorting criteria can be provided in further parameters.
EVALUATE
TOPN (
3,
ADDCOLUMNS (
VALUES ( 'Product'[Product Name] ),
"@Sales Amount", MROUND ( [Sales Amount], 500000 )
),
[@Sales Amount],
DESC,
[Product Name],
ASC
)
ORDER BY [@Sales Amount] DESC
| Product Name | @Sales Amount |
|---|---|
| Adventure Works 26″ 720p LCD HDTV M140 Silver | 1500000 |
| Contoso Projector 1080p X980 White | 500000 |
| SV 16xDVD M360 Black | 500000 |
| A. Datum SLR Camera X137 Grey | 500000 |
| Contoso Telephoto Conversion Lens X400 Silver | 500000 |
| Product Name | @Sales Amount |
|---|---|
| Adventure Works 26″ 720p LCD HDTV M140 Silver | 1500000 |
| Contoso Projector 1080p X980 White | 500000 |
| A. Datum SLR Camera X137 Grey | 500000 |
Related articles
Learn more about TOPN in the following articles:
-
Displaying filter context in Power BI Tooltips
This article describes how to display the filter context applied to a calculation using a special DAX measure in Power BI Tooltips. » Read more
-
Table and column references using DAX variables
This article describes how to correctly use column references when manipulating tables assigned to DAX variables, avoiding syntax errors and making the code easier to read and maintain. » Read more
-
Filtering the Top 3 products for each category in Power BI
This article describes different techniques to display the first three products for each category in Power BI. It includes considerations on how to adapt the technique to different models and requirements. » Read more
-
Showing the top 5 products and Other row
This article shows how to add an additional “other” row to the selection obtained using the Top N filter in a Power BI report. » Read more
-
Filtering the top products alongside the other products in Power BI
This article shows an optimized DAX technique to display the first N products for each category in Power BI, adding a row that aggregates the value for all the other products. The companion video introduces the scenario and the general approach, while this article offers further insights and optimization solutions. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/topn-function-dax