Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.