Forum Discussion

PauloH's avatar
PauloH
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...
Need Help
  • Smauro's avatar
    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

Smauro's avatar
Smauro
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

  • wwhittenton's avatar
    wwhittenton
    Helper II
    8 years ago

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

    • Smauro's avatar
      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's avatar
        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.

  • tdemoss's avatar
    tdemoss
    New Member
    8 years ago

    I was battling with this same issue and this worked for me as well!  Thank you!!!