topic Re: Direct Report to Leader and indirect report to Leader counts in DAX Commands and Tips

Direct Report to Leader and indirect report to Leader counts

vsk7775 — Mon, 27 Apr 2020 12:07:07 GMT

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 ) )

Re: Direct Report to Leader and indirect report to Leader counts

AlB — Mon, 27 Apr 2020 12:48:15 GMT

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

Re: Direct Report to Leader and indirect report to Leader counts

vsk7775 — Mon, 27 Apr 2020 14:00:03 GMT

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.

Re: Direct Report to Leader and indirect report to Leader counts

Anonymous — Mon, 27 Apr 2020 14:26:44 GMT

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

Re: Direct Report to Leader and indirect report to Leader counts

vsk7775 — Mon, 27 Apr 2020 15:33:58 GMT

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.

Re: Direct Report to Leader and indirect report to Leader counts

Anonymous — Mon, 27 Apr 2020 15:56:18 GMT

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

Re: Direct Report to Leader and indirect report to Leader counts

camargos88 — Mon, 27 Apr 2020 20:06:58 GMT

Hi vsk7775 ,

Check this file: Download PBIX

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

Ricardo

Re: Direct Report to Leader and indirect report to Leader counts

vsk7775 — Mon, 27 Apr 2020 20:37:46 GMT

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.

Re: Direct Report to Leader and indirect report to Leader counts

camargos88 — Mon, 27 Apr 2020 20:50:22 GMT

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

Re: Direct Report to Leader and indirect report to Leader counts

vsk7775 — Mon, 27 Apr 2020 21:33:13 GMT

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.