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
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!