DAX PATH but with key value pair

[JoeFields]

Is there a way to use PATH but with a key-value-pair? I have a parent-child relationship table that has GUIDs for the keys and the readable name for the values and I want to add a column to display the path/tree to each item from the top level. PATH appears to only work with the key but what if you want the displayed text to be a different column value?

Here is an example of a snip from my table:

I want to add a column for the path so it would look kind of like this:

PATH:

Community Center

Community Center > BASEMENT

Community Center > BASEMENT > ELECTRICAL ROOM_B003

...

My parent-child relationships can have varying depth, possibly beyond 10 levels, so I would rather not build manual logic to inspect and lookup every level if at all possible but without a loop or ForEach option in DAX I am not sure there is a clean way to do this.

[Jos_Woolley]

Hi,

NewColumn =
VAR ThisPath =
    PATH( 'Table'[id], 'Table'[parent_id] )
RETURN
    CONCATENATEX(
        FILTER( 'Table', PATHCONTAINS( ThisPath, 'Table'[id] ) ),
        'Table'[name],
        " > "
    )

Regards

[JoeFields]

Wow, that is really cool. Thanks! I didn't think it would be that simple.

One thing I noticed is this doesn't handle cases where the IDs are not necessarily in the table in the order from top to bottom. I found some cases where my data records where ordered differently and thus I had the path names in different orders. Fairly easy work around was to add a column indicating the path length and then use that in the CONCATENATEX OrderBy section like this:

Location Tree = 
VAR LocationPath = PATH(locations_nodes[id],locations_nodes[parentID])
VAR LocationTable = 
ADDCOLUMNS(
    FILTER(locations_nodes,PATHCONTAINS(LocationPath,locations_nodes[id]) && locations_nodes[parent_id]<>BLANK()),
    "PathPosition",PATHLENGTH(PATH(locations_nodes[id],locations_nodes[parentID])))
RETURN CONCATENATEX(
    LocationTable,
    locations_nodes[name],
    " > ",
    [PathPosition],ASC
    )