cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

PATH with no blank parent field in root

I am trying to create a hierarchical tree using the PATH function based on the article https://www.daxpatterns.com/parent-child-hierarchies/.

 

My table (imported from DB) has the typical OBJECT and PARENT columns, however at the root level, the PARENT has a value referring to another table and is thus not blank. When i try to build the hierarchy column using

 

= PATH ( [OBJECT], [PARENT] ) 

 

i get the error (freely translated): Value 'XXXXXX' in Table[PARENT] must also exist in Table[OBJECT]. Add the missing data and try again.

 

I am able to identify the roots by a logical condition (say [PARENT] = [OTHER_COLUMN]). I have tried to encapsulate the PATH call in an IF() clause, like

 

= IF (

        [PARENT] <> [OTHER_COLUMN],

        PATH([OBJECT],[PATH])

    )

 

 

but i still get the same error. Any ideas how to handle this? Any help is appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: PATH with no blank parent field in root

I found a workaround in this post: https://powerpivotpro.com/2017/12/imagine-people-tables/

 

This created a new parent column with root-nodes referring to itself as parent, which allows the PATH() function to do it's work.

 

 

NewParent = 
IF(
    COUNTROWS(
        FILTER(
            TABLE,
            TABLE[OBJECT] = EARLIER(TABLE[PARENT])
        )
    ) = 0,
    TABLE[OBJECT],
    TABLE[PARENT]
)

 

View solution in original post

1 REPLY 1
Highlighted
Frequent Visitor

Re: PATH with no blank parent field in root

I found a workaround in this post: https://powerpivotpro.com/2017/12/imagine-people-tables/

 

This created a new parent column with root-nodes referring to itself as parent, which allows the PATH() function to do it's work.

 

 

NewParent = 
IF(
    COUNTROWS(
        FILTER(
            TABLE,
            TABLE[OBJECT] = EARLIER(TABLE[PARENT])
        )
    ) = 0,
    TABLE[OBJECT],
    TABLE[PARENT]
)

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors