How to build a tree/hierarchy based on IDs?

[nick-evans]

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)

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. 

Is there a better way to do this?

[erik_tarnvik]

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/

[nick-evans]

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 )

[erik_tarnvik · 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.

[nick-evans]

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.

[erik_tarnvik]

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

[nick-evans]

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 😄 

[erik_tarnvik]

My pleasure, glad to help!

[erik_tarnvik]

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?

[erik_tarnvik]

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.