Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vickyd
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
az38
Community Champion
Community Champion

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
az38
Community Champion
Community Champion

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

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! 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors