Direct Report to Leader and indirect report to Leader counts

[vsk7775]

Hi all

I am trying to findout Direct Reporting leader and indirect report to the leader for the below dataset. I tried different DAX expression but i didn't get the right result. 

EmployeeID	LeaderID	EmployeeTitle
1		CEO
2	1	VP
3	2	VP
4	2	Manager1
5	3	Manager2
6	3	Manager3
7	4	Security engineer
8	5	Developer
9	4	Analyst
10	6	Developer II
11	5	Developer II
12	8	Manager4

When I looked for direct employee count for leaderID  2 by using the below DAX expression it is giving me the result as 5 but actually in the data set if we see it is only two people who directly report to employeeID 2 they are ( employeeid 3 and 4)

Also for indirect employee count for leaderID 2 i am getting the count as 12 but actually we have only 7 indirect employee's

Here are my queries which i used to create measures.

Direct Count:=
VAR level1 =
INTERSECT ( ALL ( [Employee ID] ), VALUES ( [Leader ID] ))
RETURN
CALCULATE ( COUNT ( 'Employee ID] ), ( level1))



Indirect Count:=
VAR level1 =
INTERSECT ( ALL ( [Leader ID] ), VALUES ( [Employee ID] ))
VAR level2 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level1)
)
VAR level3 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level2)
)
VAR level4 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level3)
)
VAR level5 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level4)
)
VAR level6 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level5)
)
VAR level7 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level6)
)
VAR level8 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level7)
)
VAR level9 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level8)
)
VAR level10 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level9)
)
VAR level11 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level10)
)
VAR level12 =
INTERSECT (
ALL ([Leader ID]),
CALCULATETABLE ( VALUES ( [Employee ID]), level11)
)
RETURN
CALCULATE ( DISTINCTCOUNT ( [Employee ID] ), UNION ( level1, level2, level3, level4, level5, level6, level7, level8, level9, level10, level11, level12 ) )

[camargos88]

Hi @vsk7775 ,

Check this file: Download PBIX 

However I got the employee_id 12 as indirect for leader 2, is it correct ?

Ricardo

[vsk7775]

Hi @camargos88 

Thanks for looking into it. I am unable to open the file do you mind sharing the measure for both direct and indirect so that i will check from my end.

[camargos88]

Hi @vsk7775 ,

Create these 2 calculated columns:

Path = PATH('Table'[EmployeeID ]; 'Table'[LeaderID])

Length = PATHLENGTH('Table'[Path])

and these 3 measures:

_Direct Report = CALCULATE(COUNT('Table'[EmployeeID ]); FILTER(ALL('Table'); 'Table'[LeaderID] = SELECTEDVALUE('Table'[EmployeeID ]))) + 0

_Indirect Report =

VAR _employeeID = SELECTEDVALUE('Table'[EmployeeID ])

VAR _length = CALCULATE(DISTINCT('Table'[Length]); FILTER(ALL('Table'); 'Table'[EmployeeID ] = _employeeID))

RETURN

CALCULATE(DISTINCTCOUNT('Table'[EmployeeID ]);

FILTER(ALL('Table'); PATHCONTAINS(PATH('Table'[EmployeeID ]; 'Table'[LeaderID]); _employeeID) && PATHLENGTH(PATH('Table'[EmployeeID ]; 'Table'[LeaderID])) > _length + 1)) + 0

_Total Report = [_Direct Report] + [_Indirect Report]

Ricardo

[vsk7775]

Hi @camargos88 

I appreciate your quick response. Not sure why it is not working for my current data set. Thanks a lot for looking into it.

[camargos88]

@vsk7775 ,

Try downloading the file again: Download PBIX 

Ricardo

[Anonymous]

You are NOT doing it right. Please start with these:

https://www.sqlbi.com/tv/hierarchies-in-dax/
https://www.daxpatterns.com/parent-child-hierarchies/
https://simplebiinsights.com/parent-child-hierarchies-in-dax/

Don't do what you do above. Do not shoot yourself in the foot.

Best
D

[vsk7775]

Hi @Anonymous 

Thank you for the link. I have already created my organization chart hierarchy in a similar way and I have a simple measure count(employee ID) but now what i was looking for is how to get the direct team member and indirect team member counts.

[Anonymous]

Well, always remember that you can pivot a table and store two versions in your model - one with the structure across columns, another with the structure across rows - and calculate your measures on the one which is more suitable for the task at hand.

Best
D

[AlB]

Hi @vsk7775 

For the number of direct reports,  youcan create the number a calclated column in yout table:

Direct Reports = 
0 + CALCULATE(COUNT(Table1[EmployeeTitle]); Table1[LeaderID] = EARLIER(Table1[EmployeeID ]);ALL(Table1))

For the number of indirect reports,  I don't quite understand why the expected result is 7 for employeeID 2. Can you explain who exactly  would be considered an indirect report?

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[vsk7775]

Hi @AlB 

EmployeeID 2 Direct Reports are EmployeeID 3 and 4.
EmployeeID 2 InDirect Reports are EmployeeID 5,6,7,8,9,10,11

Because
EmployeeID 5,6 Directly report to 3
EmployeeID 7,9 directly reports to 4
EmployeeID 8,11 Directly reports to 5
EmplyeeID 10 directly reports to 6

I am trying to create measures not calculated fields/ columns because using these measures if I drill down in excel or power bi I should see their details.