Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
19 | |
15 |
User | Count |
---|---|
125 | |
33 | |
27 | |
24 | |
23 |