Direct - Indirect Report Help

[redwings00]

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://community.powerbi.com/t5/Desktop/how-to-get-direct-and-indirect-employees-count/m-p/329780 

What I am now looking to do:

1. Show the count of Direct Reports and Indirect Reports instead of the list of employee ID's

2. Show the employee names instead of the employee ID's

I've attached a sample pbix file: https://www.dropbox.com/s/ohs5icepwebdhs0/Direct-Indirect.pbix?dl=0 

Any assistance would be appreciated.

Thank you,

[Greg_Deckler · Accepted solution]

@redwings00 Easiest thing would be:

Measure = IF(ISBLANK([Measure - Direct Reports]),BLANK(),LEN([Measure - Direct Reports]) - LEN(SUBSTITUTE([Measure - Direct Reports],",","")) + 1)

[redwings00]

@Greg_Deckler  - Thank you so much. This works great to get the total counts. Would you know if it would be possible to show the names instead of the ID's?

[Greg_Deckler]

@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? 🙂

[redwings00]

@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

[Greg_Deckler]

@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], ", " ) )

[redwings00]

@Greg_Deckler 

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], ", " ) )

[Greg_Deckler]

@redwings00 You should explore the PATH functions.