cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yempi
Frequent Visitor

nested filter counting the rows with a condition

Hi I need to build a DAX measure to know how many people reporting to a person, in a typical Org structure.

My org data table has all employees with along with thier team lead, manager and director as separate columns.

If the person is a manager, there is no team lead for him. But there is a director on the top. 

I need to know how many people under each person. See sample data in below pic

 

I spend enough time, trying with filters/count, its not taking me anywhere. Greatly appreciate your help. 

 

org3.jpg

 

 

 

 

 

1 ACCEPTED SOLUTION
yempi
Frequent Visitor

I was able to make it work with below script

 

Team Count = 
var currentName = Sheet1[Employee Name]

var cnt = 
SWITCH(Sheet1[Position],
            "TL",
                    calculate(
                            COUNT(Sheet1[Employee Name]),
                            FILTER(Sheet1, Sheet1[Team Lead]=currentName)
                    ),
            "M",
                    calculate(
                            COUNT(Sheet1[Employee Name]),
                            FILTER(Sheet1, Sheet1[Manager]=currentName)
                    ),
            "D",
                    calculate(
                            COUNT(Sheet1[Employee Name]),
                            FILTER(Sheet1, Sheet1[Director]=currentName)
                    ),
            0
)
return cnt

 solution.jpg

 

View solution in original post

1 REPLY 1
yempi
Frequent Visitor

I was able to make it work with below script

 

Team Count = 
var currentName = Sheet1[Employee Name]

var cnt = 
SWITCH(Sheet1[Position],
            "TL",
                    calculate(
                            COUNT(Sheet1[Employee Name]),
                            FILTER(Sheet1, Sheet1[Team Lead]=currentName)
                    ),
            "M",
                    calculate(
                            COUNT(Sheet1[Employee Name]),
                            FILTER(Sheet1, Sheet1[Manager]=currentName)
                    ),
            "D",
                    calculate(
                            COUNT(Sheet1[Employee Name]),
                            FILTER(Sheet1, Sheet1[Director]=currentName)
                    ),
            0
)
return cnt

 solution.jpg

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors