Forum Discussion

azakir's avatar
azakir
Resolver I
2 years ago
Solved

PATH() Error

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?

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      azakir
      Resolver I
      2 years ago

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