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