Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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:
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.
Strange. I just replicated your data and used the same exact formula and it works for me.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |