Forum Discussion
Creating a Hierarchy in Power BI
Sorry ,its a type mistake.
We are looing for Count of employees under each manager direct and indirect reportees.
Thanks in Advance
Suppose the dataset is as the 3 columns highlighted. Create a calculated column [path] and table [calculatedTable] as below. Then use a measure subordinates in the chart.
path = PATH(Employees[Name],Employees[Parent])
CalculatedTable =
FILTER (
CROSSJOIN (
FILTER (
ALL ( Employees[Name], Employees[Title] ),
Employees[Title] = "manager"
),
ALL ( Employees[path] )
),
PATHCONTAINS ( Employees[path], Employees[Name] )
)
subordinates = COUNTA(CalculatedTable[path])-1
You can check more details in the attached pbix. Also check Understanding Functions for Parent-Child Hierarchies in DAX.
If it solves your problem, please accept it as solution. For any question, feel free to let me know.
Where does this code go?
Thanks a lot Eric, this has solved my problem. I wouldn't have been able to do it without this post; kudos.
Hi Eric,
Thank you for this interesting piece of code; however, where do you enter the code for calculatedtable?
Hi Eric,
Thank you for this interesting piece of code; however, where do you enter the code for calculatedtable?
Where to enter the
CalculatedTable ?
