Direct & Indirect Count of Employees

Suppose following is the Line of Reporting in an Organization

You are provided with the following table…Basically a table representation of above picture

Manager ID	Emp ID
Ali	Kashmiri
Ali	AlZayed
Kashmiri	Fahd
Kashmiri	Sultan
Fahd	Nawar
Fahd	Ajez
Sultan	Zubair
Sultan	Ismael
Zubair	Hasan
AlZayed	Faisal
AlZayed	Alwadea

OUTPUT DESIRED

For each manager, you are required to write a DAX MEASURE to calculate the

1. Count of Direct Employees/Sub ordinates
2. Count of InDirect Employees/Sub ordinates

Following is the Final Output

	Direct Employees	Indirect Employees
Ali	2	9
AlZayed	2
Fahd	2
Kashmiri	2	5
Sultan	2	1
Zubair	1

SOLUTION

Before looking at the SOLUTION, please try solving it yourself. I would love to see if there is an alternative or better solution

Direct Employee Count is very easy to compute

Direct Employees =
DISTINCTCOUNT ( TableName[Emp ID] )

The Challenge lies in computing the InDirect Employee Count

You have to go back and forth from Manager Column to Employee Column to identify Indirect Employees upto the last line of Reporting.

Each Indirect Level of Reporting is linked to previous level of Reporting

The MEASURE that will do the job is

Indirect Employees =
VAR level1 =
 INTERSECT ( ALL ( TableName[Manager ID] ), VALUES ( TableName[Emp ID] ) )
VAR level2 =
 INTERSECT (
 ALL ( TableName[Manager ID] ),
 CALCULATETABLE ( VALUES ( TableName[Emp ID] ), level1 )
 )
VAR level3 =
 INTERSECT (
 ALL ( TableName[Manager ID] ),
 CALCULATETABLE ( VALUES ( TableName[Emp ID] ), level2 )
 )
RETURN
 CALCULATE (
 DISTINCTCOUNT ( TableName[Emp ID] ),
 UNION ( level1, level2, level3 )
 )

The Total levels of reporting in this example are 4 and Indirect levels are 3. If the Levels increase, we would have to accordingly adjust the MEASURE

Lets take one Manager “KASHMIRI” to understand how the MEASURE works

VAR level1 =
 INTERSECT ( ALL ( TableName[Manager ID] ), VALUES ( TableName[Emp ID] ) )

This returns the Managers for First Indirect level for “Kashmiri”. The picture below will make it clear

Now

VAR level2 =
 INTERSECT (
 ALL ( TableName[Manager ID] ),
 CALCULATETABLE ( VALUES ( TableName[Emp ID] ), level1 )
 )

This returns the Managers for Second Indirect level for “Kashmiri”. The picture below will make it clear

i.e.

And so

Finally, this part of the DAX measure will return the total Indirect Employees

RETURN CALCULATE ( DISTINCTCOUNT ( TableName[Emp ID] ), 
UNION ( level1, level2, level3 ) )