Create a hierarchy in PB Desktop from a self referencing table

[johnchill]

Hello, I have a table of location data that is self referencing other data in the table. I know I can create a hierarchy in desktop by using ParentLocationId and Id but I want the Name to appear as opposed to the ParentLocationId or Id. So instead of a hierachy that looked like: 63-2-1092, it would look like United States-U.S. West-Honolulu

ParentLocationId	Id	Name
                              63	United States
63	                      2	U.S. Mid-Atlantic
63	                      61	U.S. West
61	                      1092	Honolulu

 I know I can create a hierarchy in desktop but I want the Name to appear as opposed to the Parent ID and ID. Is there a way to do this?

Thank you.

[CahabaData]

a glance at your sample data indicates that 63-2-1092 doesn't appear possible and that 63-62-1092 would = United States-U.S. West-Honolulu.... but none of this really matters on your issue as I'll suppose your example was symbolic and not literal.

your issue is the data layout of your reference information

since these are fundamentally static - one approach is to build a look up table.  For example a look up table of zipcodes to States or State Initials to State Full Name are 2 classic look up table examples.  These static tables never change (or only very rarely) so it might be worth while to build yourself a look up table that has all valid combinations...much like zip code -  a 2 column table...one column numeric the other column its alpha equivalent.  I've never seen the format of your example but that look up table may exist out in the public domain so that you don't have to manually build it yourself

then you can join to the look up table on the number column and get the alpha value

[Datatouille · Accepted solution]

Hi @johnchill

Have a look at this article from @marcorusso: http://www.daxpatterns.com/parent-child-hierarchies/

You can use the series of "Path" Dax functions to do so.

[johnchill]

That works perfectly, thank you!