nested filter counting the rows with a condition

[Anonymous]

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. 

[Anonymous · Accepted solution]

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