Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
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.
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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |