Get a count of Direct and Indirect Reports

[vickyd]

We have data available in the format below (minus the 3 columns to the right) and want to setup measures Direct Reports, Indirect Report and Total Reportees such that when I put the employees and these measures in a table it should me the counts I've shown below. 

Don't know if I'm overthinking it but not able to get anywhere with it so far. 

[az38 · Accepted solution]

hi @vickyd 

try measures

Direct Reports =
calculate(countrows('Table');FILTER(ALL('Table');'Table'[Hard-Line Manager]=SELECTEDVALUE('Table'[Employee])))

Indirect Report =  
calculate(countrows('Table');FILTER(ALL('Table');
('Table'[L1 Manager]=SELECTEDVALUE('Table'[Employee])
||
'Table'[L2 Manager]=SELECTEDVALUE('Table'[Employee])
||
'Table'[L3 Manager]=SELECTEDVALUE('Table'[Employee])
||
'Table'[L4 Manager]=SELECTEDVALUE('Table'[Employee]))
))

Total Reportees = [Direct Reports] + [Indirect Report]

do not hesitate to give a kudo to useful posts and mark solutions as solution

[vickyd]

Awesome @az38  This worked like a charm!

I wasn't aware that SELECTEDVALUE could be used this way as well. Was always thinking SELECTEDVALUE only comes into play at a dashboard level when you want to know a slicer selection. 

Thanks a lot! 

[maxabele]

Hi all, 

I have the same data structure as vickyd. 

Employee	Supervisor	Level 1 Manager	Level 2 Manager	Level 3 Manager
BBB	AAA	ZZZ	YYY	AAA
CCC	BBB	ZZZ	YYY	AAA
YYY	ZZZ	ZZZ	YYY

When I use the code as from above to get the # of direct reports, it is not only counting the directs, but also the indirect reports. 

I tried using calculated columns and measures (measures did not work at all, returned blanks)

Do you have any idea what I'd need to change to make it work? 

Thanks in advance and best,

Max