Count number of Direct Reports

[Anonymous]

Hi,

I have three columns in my table - EmpID, Supervisor and Level. I need to find out count of number of direct reports for a particular EmpID using the Supervisor column but only for the Leader, Manager and Director levels. The count of number of direct reports should be populated in a new column (CountofDirectReports). I highlighted CountofDirectReports column in yellow where the count needs to go. I working to figure it out using DAX but could not figure it out. Can someone please help?

Here is the sample data as an example:

Thanks for your help!

[jdbuchanan71 · Accepted solution]

Hello @Anonymous 

You can use a measure like so to add a calculated column to your table:

DirectReports = 
VAR CurrentEmp = 'Table'[EmpID]
RETURN
CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
    'Table'[Supervisor] = CurrentEmp
)

[Anonymous]

This worked. Thank you for your help!