Forum Discussion
Span of Control
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
Hi Smauro, any idea of how could this work in case there're duplicates? i.e. having different snapshot periods (months), hence getting some duplicates in the Employee and Manager column.
Helper IIHi PauloH,
Since you said you have snapshots, I'm assuming you want the most recent manager for a given employee (they could change over time). I built that into the calculation. This will require a new table, achieved by going to the Modeling tab and selecting "New Table". Then, use the following:
Span of Control = SUMMARIZE( Sheet1 , Sheet1[employee] , "Date" , MAX(Sheet1[Date]) , "manager" , calculate(max(Sheet1[manager]), FILTER(Sheet1 , Sheet1[Date] = MAX(Sheet1[Date]))))
This will return a distinct list of employees with their respective manager for the most recent time period. Once that's done, you can follow the code you used originally in an additional column:
SpanOfControl =
var x = 'Span of Control'[employee]
return
CALCULATE( COUNTROWS('Span of Control') , FILTER( 'Span of Control', x = 'Span of Control'[manager] ))I hope this helps!
Best,
Will
Hi wwhittenton, it could be a bit more complicated, as users should be able to filter by dates. I'll use below as input and see how I can make it work. Thanks!