DAX to return highest and lowest in a group

[ak_sow8994]

Hi All,

I'm pretty new to DAX. So, struggling to get the expression right.

Problem : Create a tabular report with following columns:

Manager Name, Manager Department, Highest Salaried Person under him, Number of employees under him.

My Approach: So far, I have loaded the data set and merged query (self join) EMP.managerid = EMP1. Employee ID...

added a column to insert  row_number() over(partition by manager order by salary ) 

But got stuck at how to get the corresponding employee name using row number..

Or is there any easy way to directly return employee with highest salary grouped by employer using DAX?

Data set: 

Employee ID	Emp Name	Manager ID	Department	Emp City	Salary
1	Har**bleep**	10	10	Toronto	9500
2	Mohan	10	10	Palo Alto	9200
4	Steve	4	10	Norcross	12000
10	Madhavan	4	10	Palo Alto	10000
23	Deepak	4	10	Toronto	9000
24	Vineet	4	10	Toronto	9100

Expected Result:

[ak_sow8994]

@amitchandak Thanks a lot for quick reply. And thanks for your inputs. TOPN worked.

Next time, I'll make sure to frame my question clearly..

[amitchandak · Accepted solution]

@ak_sow8994 , Not very clear , You can create Rank and filter for 1. You can create top

rankx(allselected(Table[Employee]), [salary measure],,desc,dense)

use visual level filter Rank =1 

Or try TOPN

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

TOPN: https://www.youtube.com/watch?v=QIVEFp-QiOk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns