Building Org Structure - Parent_Child Structure

Hi Dmitry

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

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

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

Thanks
Rykie

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.

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.

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

Hi Dmitry

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


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

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

Thank you

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

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

1 Like

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], '|')
2 Likes

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

Thank you, Dmitry.

This is a great enhancement. :clap::clap::clap:

1 Like

Great! Happy to hear that :slight_smile:

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 ?

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)

1 Like