cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnchill
Helper I
Helper I

Create a hierarchy in PB Desktop from a self referencing table

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.

1 ACCEPTED SOLUTION

Hi @johnchill

 

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

 

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

View solution in original post

3 REPLIES 3
CahabaData
Memorable Member
Memorable Member

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

www.CahabaData.com

Hi @johnchill

 

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

 

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

That works perfectly, thank you!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!