Forum Discussion
Direct - Indirect Report Help
redwings00 Easiest thing would be:
Measure = IF(ISBLANK([Measure - Direct Reports]),BLANK(),LEN([Measure - Direct Reports]) - LEN(SUBSTITUTE([Measure - Direct Reports],",","")) + 1)
redwings00 Like:
Measure - Direct Reports 1 =
VAR __empid = MAX('Sheet1'[EMP ID])
VAR direct = FILTER(ALL('Sheet1'),[MANAGER ID] = __empid)
RETURN
IF ( ISEMPTY ( direct ) = FALSE (), CONCATENATEX ( direct, [EMPLOYEE NAME], ", " ) )I tell people not to use VALUES and CALCULATE but does anyone listen? 🙂
Greg_Deckler Thank you again Greg. Would you be able to help with the measure for the indirect names if possible?
I was not aware of the information regarding not to use VALUES and CALCULATE, but will definitely need to read it as I get more comfortable in power BI
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.