Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ak_sow8994
Regular Visitor

DAX to return highest and lowest in a group

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 IDEmp NameManager IDDepartmentEmp CitySalary
1Har**bleep**1010Toronto9500
2Mohan1010Palo Alto9200
4Steve410Norcross12000
10Madhavan410Palo Alto10000
23Deepak410Toronto9000
24Vineet410Toronto9100

 

Expected Result:

RESULT.PNG

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
ak_sow8994
Regular Visitor

@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
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors