Forum Discussion
Advocate IHow 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)
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?
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.
9 Replies
- erik_tarnvik
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:
- 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.
nick-evansPath 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
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?