Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |