Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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?
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
98 | |
89 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |