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 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:
Regards
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], '|')
You literally just saved me so much time just now! Thanks for this feature!
Thank you, Dmitry.
This is a great enhancement. 
Great! Happy to hear that 
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)
Hi Dmitry,
I have a parent-child table that I’d like to flatten. It consists of an ID column and a second column that represents the child elements, separated by a semicolon. Sometimes, this column also includes an operator (+ or -) or an operation indicator (Reset or Add Difference).
I tried using the Create Hierarchy action after splitting the second column into rows, but it always raises an error. I also tried the Repeat action, but then I get a message saying there are too many loops.
Could you tell me how to achieve this without having to perform as many outer joins as there are possible values?
I’m sending you a sample dataset.
ParentChild.csv (1.6 KB)
Thanks and best regards,
Chris
Hi Chris,
The "Hierarchy path" action correctly raises an error, because your data structure is not a parent-child tree, it's a graph.
In a parent-child tree, any child has one and only one parent. However, in your sample data, this is not the case, no matter if we consider ID or Content as children:
The [Content] column can't be children because for the "child" Content=3, there are multiple "parents" ID=1 and ID=5.
The [ID] column can't be children because for the "child" ID=3 there are multiple "parents" Content=7 and Content=8.
Therefore, it's not a one-to-many or many-to-one relationship. It's many-to-many, i.e. graph.
Strictly speaking, a parent-child tree is a particular case of a directed graph in which nodes have a one-to-many relationship.
Hi Dmitry,
Indeed it's not a parent-child hierarchy, but how do I then build a table with each ID and associated (sub)content with, I guess, the repeat action ? The goal is to merge a second table where the ID is the link, and contains a list of charts of accounts (eg ID 1 has 2,3,4 as "children" and in the charts of accounts 1 is linked to 205000, 2 to 300100, and so on...).
Thanks and regards,
Chris
If you split the separated values into rows, you will receive a table with two columns. This would be a list of edges of that graph. For instance:
3 -> 1
3 -> 5
7 -> 3
8 -> 3
The table above corresponds to this graph:
This graph is not a tree because children in it (e.g., node 3) can have more than one parent.
You can merge to this table another table, or itself to obtain paths in this graph. For instance, if you do an inner merge with itself, you will receive a list of all 2-hop paths in this graph:
7 -> 3 -> 1
7 -> 3 -> 5
8 -> 3 -> 1
8 -> 3 -> 5
You can technically repeat merging the table with itself (using the "Repeat" action) until you receive all paths in the graph. This is probably the closest equivalent of receiving all hierarchy paths in a tree-like graph.
Maybe, @cvo can add more to the conversation.
Hi Dmitry,
Thanks for your answer. I fully understand the concept and the need to merge the table with itself to get the list of nodes, but I don't understand how to use the 'Repeat' action to achieve this. I tried to follow your example with the path list, but I have to admit that I'm stuck.
Kind regards,
Chris
Hi Chris,
Where are you stuck ?
I guess you are trying to make a self-join of the resulting table with itself in a repeat loop
In such a case you'll get an exploration of your graph that will change the"starting point" of your exploration on each step of the loop
One trick can be to export the base table and to make the join of your resulting table with this base table in each loop.
In this case the repeat action will behave like a "with recursive" SQL query.
Maybe have a look at this post
and to the join file "EM repeat with table.zip" in the post
Regard
Hello,
Thanks for your answer. I tried to follow your example but couldn't figure out how to use the repeat action with my dataset. I did eight left joins to the main table and got the expected result. I will try again later, when I have some time, to fully understand how you did this.
Thanks and regards,
Chris