Forum Discussion
Solved
Direct - Indirect Report Help
Hello, I am looking for some assistance with showing Direct and Indirect reports. This previous thread below provided a great start for myself but I am now trying to expand on this. https://com...
redwings00 Easiest thing would be:
Measure = IF(ISBLANK([Measure - Direct Reports]),BLANK(),LEN([Measure - Direct Reports]) - LEN(SUBSTITUTE([Measure - Direct Reports],",","")) + 1)
redwings00 Well, that's just my opinion on VALUES and CALCULATE. Indirect is a very simple change:
Measure - Indirect Reports 1 =
VAR __empid = MAX('Sheet1'[EMP ID])
VAR direct = SELECTCOLUMNS(FILTER(ALL('Sheet1'),[MANAGER ID] = __empid),"__EMP_ID",[EMP ID])
VAR indirect = FILTER(ALL('Sheet1'),[MANAGER ID] IN direct)
RETURN
IF ( ISEMPTY ( indirect ) = FALSE (), CONCATENATEX ( indirect, [EMPLOYEE NAME], ", " ) )
I'm now wanting to add further layers to get the child relationship of the indirects and so on, I have tried this, but I'm receiving an error: Function CONTAINSROW must hav a value for each column in the table expression.
If I could get all the Indirect and child names into one measure that would be more efficient. I think I have up to 8 child layers
Measure - CHILD Reports 1 =
VAR __empid = MAX('Sheet1'[EMP ID])
VAR direct = SELECTCOLUMNS(FILTER(ALL('Sheet1'),[MANAGER ID] = __empid),"__EMP_ID",[EMP ID])
VAR indirect = FILTER(ALL('Sheet1'),[MANAGER ID] IN direct)
VAR child = FILTER(ALL('Sheet1'),[Manager ID] IN indirect
RETURN
IF ( ISEMPTY ( child ) = FALSE (), CONCATENATEX ( child, [EMPLOYEE NAME], ", " ) )
redwings00 You should explore the PATH functions.