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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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 13K+ 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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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