Another commonly used function is the TOPN function. It is used for both TOP and BOTTOM use cases. In this example to demonstrate the basics we are first going to simply fetch the top 34employees by their employee key.
The below Formula shows what this would look like
Table = TOPN(4,ALL('Dimension Employee'),'Dimension Employee'[Employee Key],ASC)
To get the bottom four we simply change the ASC to DESC
Now let us look at a more realistic example of identifying the top sales person by Sales. As can be seen from the formula below we simply replace the Employee Key with the calculation for sum of Total Incd Tax.
Table = TOPN(4 , ALL('Dimension Employee'),CALCULATE(sum('Fact Order'[Total Including Tax])),DESC)Keep in mind we have also included the use of the ALL function to ensure the calculation ignores any filters that may have been applied.
The result from SQL as well as PowerBI have been shown below for comparison.
The SQL equivalent of the above DAX is similar to the query below
SELECT TOP 4 e.Employee
,sum(o.[Total Including Tax])
FROM Dimension.Employee e
INNER JOIN fact.[Order] o ON e.[Employee Key] = o.[Salesperson Key]
GROUP BY e.Employee
ORDER BY sum(o.[Total Including Tax]) DESC
Please Consider Subscribing
