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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Parent Child Hierarchy all in one column from data base


I'm new to Power BI, and i'm in charge of handling with Parent Child Hierarchy. But this kind of hierarchy is different from the ones which i studied. See for yourself:


I'm confused on how could i use path and pathcontains in this situation. If i split the column by the delimiter, then i can have several levels, but what should i do next? 

In the application, the level "0x.0x" is always higher than the "0x.0x.0x", even though they are in the same group.

Frequent Visitor

I'm trying to reply to you, but for some reason i'm unable to do it with a table.

So, i modified the data, replacing the dot for the "|" and then i divided the column in 8 levels. Does this make sense?

Captura de tela 2024-04-15 133328.png

I have a employee column, ID column, company, cost center etc. if its for good use. I'm in the right path?

Super User
Super User

Hello @PauloRicardo,


Can you please try this approach:


1. Create a Calculated Table

HierarchyTable = 
    DISTINCT ( YourOriginalTable[SECTOR] ),
    "POSITION", LOOKUPVALUE(YourOriginalTable[POSITION], YourOriginalTable[SECTOR], YourOriginalTable[SECTOR]),
        SUBSTITUTE(YourOriginalTable[SECTOR], ".", "|"), 

2. Fix the ParentSector Calculation

ParentSector = 
VAR CurrentSector = YourOriginalTable[SECTOR]
VAR ParentPath = SUBSTITUTE(CurrentSector, ".", "|")
VAR ParentCount = PATHLENGTH(ParentPath)
RETURN IF(ParentCount > 1, PATHITEMREVERSE(ParentPath, 2, "|"), BLANK())

Hope this helps!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website:
➤ Email:
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views):
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.