cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Get a count of Direct and Indirect Reports

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. 

Manager.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

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


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

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

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


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

View solution in original post

Highlighted

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! 

Highlighted

Hi all, 

 

I have the same data structure as vickyd. 

 

EmployeeSupervisorLevel 1 ManagerLevel 2 ManagerLevel 3 Manager
BBBAAAZZZYYYAAA
CCCBBBZZZYYYAAA
YYYZZZZZZYYY 

 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors