Parent Child Hierarchy all in one column from data base

[PauloRicardo]

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
1	Executive
01.01	Especialist
01.01.01	Professional
01.01.01.01.01.01	Especialist
01.01.01.01.01.01.01	Especialist
01.02	Executive
01.02.01	Management
01.02.01.01	Coordenation
01.02.01.01.01	Professional
01.02.01.02.01.01.01	Professional
01.02.02.01.01	Professional
01.02.02.01.01.01.01	Professional
01.02.02.02.01	Coordenation
01.02.02.02.01.01.01	Professional
01.03.01	Management
01.03.01.01.01	Operacional
01.03.01.01.01.01.01	Incumbent

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.

[PauloRicardo]

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?



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

[Sahir_Maharaj]

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!