Building Org Structure - Parent_Child Structure

[Rykie]

Hi Dmitry

Can anyone direct me on how to build a structure similar to using Path, Pathlength and as in PowerBi?

Parsing Organizational Hierarchy or Chart of Accounts in Power BI with...

Path = PATH(DimEmployee[EmployeeKey],DimEmployee[ParentEmployeeKey])
Path Length = PATHLENGTH(DimEmployee[Path])

Organization Level 1 = PATHITEM(
DimEmployee[Path],
1,
1)

Thanks
Rykie

[dgudkov]

Hi Rykie,

Check out this article - it should be a good starting point: How to process parent-child data structures

Let me know if you get stuck.

[Rykie]

Thanks, Dmitry.

I read the article, but am still struggling.

I might just stick to PowerBI to create org structure as it is easier for me.

[dgudkov]

I’m curious what’s the use case for the path and path length? Is it something purely Power BI-related or not?

[Rykie]

Hi Dmitry

This is a dax formula used in PowerBI to create an org structure.
It is efficient and powerful.

Understanding functions for parent-child hierarchies in DAX - DAX

It can be used for accounts/ products/ org structure or anything where you need parent-child relationship.

It would be great if something similar exists in EM

[James_z9]

Landed here looking for the same functionality, any chance it now exists?

Thank you

[cvo]

Greetings,

Parent-child structures are quite tricky in EM, but you can get very powerful results.
You can even simulate a reccursive outer-join.
If you use the Repeat transformation and make a self join in the loop, you'll get a relative path because you make a self join on the result of the previous iteration.
If you want a "classical" structure, you need to join the result of the repeat iteration with the source table

This is explained in the project shared on this link

Repeat table as iterate table

Greetings, with the iterate table, we can operate transformations on a reference table. Could we have the same with repeat. To reproduce a ‘with recursive’ SQL query in EM, we currently need to save the reference table as a dset file and import it into the repeated module. the possibility to transfer a table would make process cleaner and maybe improve performance as with the iterate table transformation. Regards

it uses the accounts tree from adventure works DWH

I made a few changes in this one to get the flattened dimension by levels
EM repeat with table expanded levels.zip (7.5 KB)

You can customized with filters, tests or calculation within the loops. This is where the power of the Repeat transformation appears. You can customize a DFS with selecting the path that gives flatter structure or make a bottom-up then top down exploration:

• A -> B -> C
• E -> D -> C
• F -> B
  =>
• A -> B -> C -> D -> E
• A -> B -> F

Regards

[dgudkov]

In v5.4.1, we’ve added the “Hierarchy path” action. It’s similar to the Path function in Power BI.

To calculate the path length, count the number of separators and add 1. For instance:

substringcount([Path], '|') + 1

To pick a particular item (node) from the path, use the pick() function. For instance:

pick(2, [Path], '|')

[Shaheed_Fazal]

You literally just saved me so much time just now! Thanks for this feature!

[Rykie]

Thank you, Dmitry.

This is a great enhancement.

[dgudkov]

Great! Happy to hear that

[cvo]

General ledger, bill of material, organisation charts, most of the ERP, banking software are based on parent-child structures. Very few data transformation software can handle them. I really wonder why ?

[cvo]

Greetings,

Sometimes you need to have all the elements that have a specific upper element, sometimes you want to have the reachability (all the elements that you can reach up or down).
Maybe this will help.

Regards
reachability.morph (8.2 KB)
DimAccount.dset (4.2 KB)