Forum Discussion
Advocate IHow to build a tree/hierarchy based on IDs?
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.
Solution SpecialistIndeed 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?
- erik_tarnvik
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.