Top N Vs RANKX in DAX
Top N and RANKX are two different DAX functions used in Power BI to perform ranking and filtering operations on data.
- Top N is used to filter data and return the top N rows, while
- RANKX is used to assign a rank to each row in a table based on a specific column and its values.
Both functions can be used in Power BI to perform ranking and filtering operations, depending on the specific needs and requirements of the analysis.
What's TOPN in DAX?
TopN is a DAX function that filters data and returns only the top N rows based on a specific column and its values.
The syntax for Top N in DAX is:
TOPN ( <N>, <Table>, [<ColumnName>], [<OrderType>] )
Where <N> is the number of rows to return, <Table> is the name of the table to filter, [<OrderType>] 0 ASC, 1 DESC, and <ColumnName> is the name of the column to use for ranking.
When should you use the TOPN in DAX?
You can use Top N to return the top 10 products with the highest Amount, or the top 10 customers with the most orders.
Ex:
You can create a new table with the below formula to return the top 10 products with the highest Amount
Top 10 Products = TOPN(10, Products,Products[Amount],DESC)
Output
Note: This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
What's RANKX in DAX?
RANKX, on the other hand, is a DAX function used to assign a rank to each row in a table based on a specific column and its values.
The syntax for RANKX in DAX is:
RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
Where <Table> is the name of the table to rank, <Expression> is the column or expression to rank by, <Value> is the value to assign the rank to, <Order> is the order of the ranking (ascending or descending), and <Ties> is the method to handle ties (whether to assign the same rank to tied values or skip ranks).
When should you use the RANKX in DAX?
You can use RANKX to assign a rank to each product based on its amount, or to each customer based on their orders.
Ex:
- Open Power BI Desktop and connect to your data source.
- Go to the "Modeling" tab and create a new measure by clicking on "New Measure".
- Name the measure "Rank by Sales" and use the following DAX formula:
Formula
Rank by Sales =
RANKX(
ALL('Products'[Product]),
CALCULATE(SUM('Sales'[Amount]))
)
This formula calculates the total sales for each product and ranks them based on their sales amount. The "ALL" function removes any filters applied to the "Product" column.
- Create a new table by going to the "Modeling" tab and clicking on "New Table".
- Name the table "Top 10 Products" and use the following DAX formula:
Fomrula
Top 10 Products =
FILTER(
'Products',
[Rank by Sales] <= 10
)
This formula filters the "Products" table and shows only the top 10 products based on their sales rank. The filter condition is set to show only the products whose rank is less than or equal to 10.
See Also
