Direct report leader and indirect reporting 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 ) )

[v-juanli-msft]

Hi @vsk7775 

You could refer to this similar thread.

Create measures

Direct = 
VAR direct =
    CALCULATETABLE (
        VALUES ( 'Table'[EmployeeID]),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID] = MAX ( 'Table'[EmployeeID] ) )
    )
RETURN
    IF ( ISEMPTY ( direct ) = FALSE (), CONCATENATEX ( direct, 'Table'[EmployeeID], "," ) )

direct count = IF([Direct]<>BLANK(),LEN([Direct])-LEN(SUBSTITUTE([Direct],",",""))+1)

Indirect = 
VAR direct =
    CALCULATETABLE (
        VALUES ( 'Table'[EmployeeID] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID]= MAX ( 'Table'[EmployeeID] ) )
    )
VAR indirect =
    CALCULATETABLE (
        VALUES ( 'Table'[EmployeeID] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID] IN direct )
    )
VAR child =
    CALCULATETABLE (
        VALUES ( 'Table'[EmployeeID] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID] IN indirect )
    )
RETURN
    IF (
        ISEMPTY ( indirect ) = FALSE (),
        CONCATENATEX ( UNION ( indirect, child ), 'Table'[EmployeeID] , "," )
    )

indirect count = IF([Indirect]<>BLANK(),LEN([Indirect])-LEN(SUBSTITUTE([Indirect],",",""))+1)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vsk7775]

Hi @v-juanli-msft  I tried this in my ssas tabular model cube to create this measure and I am getting all blanks in my sample file I have create the below Direct as new column and measure i tried both

Direct = 
VAR direct =
    CALCULATETABLE (
        VALUES ( 'Table'[EmployeeID]),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[LeaderID] = MAX ( 'Table'[EmployeeID] ) )
    )
RETURN
    IF ( ISEMPTY ( direct ) = FALSE (), CONCATENATEX ( direct, 'Table'[EmployeeID], "," ) )

It exactly the same as what you created 

[v-juanli-msft]

Hi @vsk7775 

Is your connection import or live connection?

Please check the difference between my pbix and yours and share a screenshot for further analysis.

Best Regards

Maggie

[vsk7775]

Hi @v-juanli-msft 

I have created the same measures in power bi for my sample data set and they are working in power bi But these are not working in SSAS Tabular model when I try to create do we need to change anything in the DAX queries I am seeing all Blanks in my tabular model measures. Also, in power bi we can see that measure as a column but not in SSAS Tabular.