Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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] )
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] )
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |