Forum Discussion

Frequent Visitor

8 years ago

Solved

Span of Control

Hi all, I have a table structured like this: Employee Manager 1 2 1 3 1 4 1 5 2 6 2 7 2 8 2 9 2 10 3 11 3 12 3 13 3 14 4 15 4...

Smauro
8 years ago
You could simply add a new column with DAX:

Span of Control = VAR x = [Employee] RETURN CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', x = [Manager] ) )
You should change the 'Table' with your table name.

Edit: You may add a " + 0" after the "COUNTROWS(TABLE)" in order to not display null results

Icon for Solution Sage rank

Solution Sage

8 years ago

You could simply add a new column with DAX:

Span of Control =
VAR x = [Employee]
RETURN
    CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', x = [Manager] ) )

You should change the 'Table' with your table name.

Edit: You may add a " + 0" after the "COUNTROWS(TABLE)" in order to not display null results

Icon for Helper II rank

Helper II

8 years ago

Assuming your employees don't repeat, Smauro gave a better solution than I did. Kudos to him.

Smauro
Solution Sage
8 years ago
wwhittenton you are right, thank you!
I took it for certain that the employees would be unique.. If not, then your solution works better.
- wwhittenton
  Helper II
  8 years ago
  Smauro In retrospect, that's the better (or perhaps only viable) assumption. If the employees are not unique, the span of control calculation comes out incorrect either way, because the manager an individual reports to would be double-counted even in the new table.
  
  Thanks for the good wishes and happy holidays to you and to you as well, PauloH.
  - Smauro
    Solution Sage
    8 years ago
    wwhittenton I think you're right. And there may be some people with more than one manager as well.
    PauloH So, in case your Employee-Manager has duplicates, you should first go on Query Editor, Select both Employee and Manager columns and right click-> remove duplicates. And then, add the column I mentioned.
    
    Happy holidays to you too wwhittenton! And PauloH :)

Related content