Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
nick-evans
Advocate I
Advocate I

How to build a tree/hierarchy based on IDs?

I have a site that has a structured hierarchy:

Top Space - Child Space - Child Space - Child Space - Items

 

The depth of each space varies, so there can be any random number of subspaces/children.

I am trying to build a 'Breadcrumb' string that tells me the full depth of any given space.

So if using the above example's second 'Child Space', it would show a breadcrumb/string of 'Top Space - Child Space'.

 

My data is stored with a Space Name, Space ID, Parent Name, Parent ID. 

(This is sample data only - my SpaceID values do not use such a clean format, they're random numbers)

treedata.png

 

I have found that I can get a spaces Parent's Parent , using this formula:

Parent2Name = LOOKUPVALUE('Taxonomy'[ParentName],'Taxonomy'[SpaceID],'Taxonomy'[ParentID])
Parent2ID = LOOKUPVALUE('Taxonomy'[ParentID],'Taxonomy'[SpaceID],'Taxonomy'[ParentID])

I can repeat these columns with incremental lookup values over and over until I have the lowest spaces breadcrumb clear to the top level. but it's wildly tedious and wouldn't be feasible in my real

Example:

Parent3Name = LOOKUPVALUE('Taxonomy'[ParentName],'Taxonomy'[SpaceID],'Taxonomy'[Parent2ID])
Parent3ID = LOOKUPVALUE('Taxonomy'[ParentID],'Taxonomy'[SpaceID],'Taxonomy'[Parent2ID])

 

Though this gets me the intended outcome, it's wildly tedious and wouldn't be feasible in my real data, because I don't necessarily know how many iterations I would need. With all these columns defined, I can then concatenate the Name values to get the string I care about for my breadcrumb, but getting to this point is really unpleasant. 

 

powerbi_data_tree.png

 

Is there a better way to do this?

 

 

1 ACCEPTED SOLUTION

Because, given your original table Table1, all you would have to do would be 

PATH(Table1[SpaceName], Table1[ParentName])

You don't need to use the ID's, it will work perfectly fine with the textual columns as long as they are distinct.

 

 

View solution in original post

9 REPLIES 9
erik_tarnvik
Solution Specialist
Solution Specialist

Indeed there is. See DAX function PATH, it should deliver your desired result directly.

 

https://msdn.microsoft.com/en-us/library/gg492167.aspx

 

Some examples and explanation can also be found here:

 

http://www.daxpatterns.com/parent-child-hierarchies/

Path looks to be EXACTLY what I'm after, thank you!

 

At my deepest level, PATH returns "1000 | 1100 | 1200 | 1300 | 1400 | 1500 | 1600 | 1700". 

In the article with examples that you linked, the author says " You have to define the maximum depth of the hierarchy in advance, planning enough levels for future growth."

Since I don't know the biggest depth, this would mean I have to just create a whole bunch of columns to store the corresponding string value at each level?

 

So is there not a way to singlurarly translate the PATH value to its corresponding string/NAME?

Ultimately I'm just trying to get that PATH value turned into: 

"Domain | Kingdom | Phylum | Class | Order | Family | Genus | Species" (or whatever the string equivelant would be based off of the PATH value). 

 

I thought maybe if I just declared a depth of 50, I would see everything provided it never got below 50 spaces deep, but that didn't work the way I expected.

 

Level50 = PATHITEM ( Taxonomy[PATH], 50 )

 

Because, given your original table Table1, all you would have to do would be 

PATH(Table1[SpaceName], Table1[ParentName])

You don't need to use the ID's, it will work perfectly fine with the textual columns as long as they are distinct.

 

 

Your understanding is correct.

At my second level, I get: Path=1000|1100

I would like it to be displayed as "Domain | Kingdom"

At the 7th level 1000|1100|...1600|1700 displayed as "Domain|...Genus|Species".

 

I tried your suggestion of 

PATH(Table1[SpaceName], Table1[ParentName])

Which is how they demonstrated the behavior in that article using peoples names, but I get no output when I use it?

Not sure if I'm supposed to be adding it differently, but when I add a new column under the Data view (which is where I built my path based on ID's), I get no output.

 

novalues.png

Strange. I just replicated your data and used the same exact formula and it works for me.

 

image.png

I have no clue what happened, but after relaunching Power BI  Desktop, it does, in fact, work in the way you said it should.  

I have exactly what I'm after! Thanks so much 😄 

My pleasure, glad to help!

Hi Nick,

i'm not sure, but it seems to me that you would like the PATH return value of "1000 | 1100 | 1200 | 1300 | 1400 | 1500 | 1600 | 1700" to appear as "Domain | Kingdom | Phylum | Class | Order | Family | Genus | Species" based on a translation of 1000 = "Domain" and so on. Is that correctly understood?

BTW there is also a way to do this in PowerQuery and M. I was curious about that and found the following:

 

https://blog.crossjoin.co.uk/2013/06/22/flattening-a-parentchild-relationship-in-data-explorer/

 

I just tried it on some sample data and it works. A little more involved but if you actually want the intermidiate columns in your question, this could be used as a starting point.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.