Calculate number of direct reports as calculated column

[enestkm]

Hi 

I am trying to calculate number of direct reports in a table and add  as a calculated column as seen below in the last column.

I have monthly data appended to the table every month and the calculation needs to calculate number of direct reports for every month seperately . 

Many thanks in advance for any solutions

[amitchandak]

@enestkm , you should try

Path and pathlength

https://docs.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax

https://simplebiinsights.com/power-bi-path-function-for-parent-child-hierarchies-in-dax/

https://docs.microsoft.com/en-us/dax/pathlength-function-dax

[mahoneypat · Accepted solution]

This expression should work for your calculated column

Direct Reports This Month =
VAR __thisemployee = Employees[EmployeeID]
RETURN
    CALCULATE (
        COUNTROWS ( Employees ),
        ALLEXCEPT ( Employees, Employees[Period] ),
        Employees[ManagerID] = __thisemployee
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat