Forum Discussion

JoeFields's avatar
JoeFields
Frequent Visitor
4 years ago

DAX PATH but with key value pair

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.

Need Help

2 Replies

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi,

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

    Regards

    • JoeFields's avatar
      JoeFields
      Frequent Visitor
      4 years ago

      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
    )