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 ...
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

     

     

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