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 t...
Need Help
Jos_Woolley's avatar
Jos_Woolley
Solution 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
    )