PATH() Error

[azakir]

Hi Guys. 

I have the following table with Manager and Employee: 

I would like to display it in a Matrix when the manager is expanded, it shows the staff reporting to the manager. Problem is, there are examples where the staff are themselves manager of other staff. Ideally, I'd like to show Raymond Latter Expanded to John Stamos and further expands to Cath Geleilo. 

@Jihwan_Kim helped out with PATH() function. However, I get the following error:

The value 'Raymond Latter' in 'HR_DW_Import'[Manager_DisplayName] must also exist in 'HR_DW_Import'[PATH CC2]. Please add the missing data and try again.

Is there a way to resolve this?

[Anonymous · Accepted solution]

Hi  @azakir ,

Here are the steps you can follow：

1. Create calculated column.

Test =
var _select=SELECTCOLUMNS('Table',"Man",[Manager_DisplayName])
var _test1=
CONCATENATEX(
    FILTER(ALL('Table'),
    'Table'[Manager_DisplayName]=EARLIER('Table'[Manager_DisplayName])),[Employee],"|")
var _table1=
SUMMARIZE(
    'Table','Table'[Manager_DisplayName],
    "Employe11",
    CONCATENATEX(
        FILTER(ALL('Table'),
        'Table'[Manager_DisplayName]=EARLIER('Table'[Manager_DisplayName])),[Employee],"|"))
var _test2=
IF(
    'Table'[Employee] in _select ,
    MAXX(
        FILTER(_table1,[Manager_DisplayName] =EARLIER('Table'[Employee])),[Employe11]))
return
_test1 &"|"&_test2

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[azakir]

thanks @Anonymous that did the trick for me. Cheers for your help