Skip to main content
cancel
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

Reply
PauloRicardo
Frequent Visitor

Parent Child Hierarchy all in one column from data base

Hi,

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:

SECTOR          POSITION
1Executive
01.01Especialist
01.01.01Professional
01.01.01.01.01.01Especialist
01.01.01.01.01.01.01Especialist
01.02Executive
01.02.01Management
01.02.01.01Coordenation
01.02.01.01.01Professional
01.02.01.02.01.01.01Professional
01.02.02.01.01Professional
01.02.02.01.01.01.01Professional
01.02.02.02.01Coordenation
01.02.02.02.01.01.01Professional
01.03.01Management
01.03.01.01.01Operacional
01.03.01.01.01.01.01Incumbent



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.

2 REPLIES 2
PauloRicardo
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?

Sahir_Maharaj
Super User
Super User

Hello @PauloRicardo,

 

Can you please try this approach:

 

1. Create a Calculated Table

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

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: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ 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): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
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.