Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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. 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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