Recursive relationships

[DmitryKo]

Let's say we have a simple table for entities and their IDs (TableA) and another table that defines relationships in a format similar to this:

IncomingID	OutgoingID	<some attributes that are irrelevant>
1	2
2	3
2	4
5	6
5	7

We need to be able to find all entities related to any given "starting" ID; in the sample data above, for ID=1, we would expect {2, 3, 4} as the correct set, as 2 is related to 1 and then both 3 and 4 are related to 2. Relationship defined that way can be recursive.

What is the correct approach to configure this as a part of the model and what kind of DAX code can be used to achieve the sample scenario?

[DmitryKo]

The relationship is a conceptual "many to many". A given item could have more than one "parent" - any pair of item1 and item2 could have from 0 to n relationships (of different type), where n is 1 or higher.

Also there could be just 1 level of nesting or 4-6 levels of nesting, depending on specific dataset. Would approach with PATH cover it?

[scee07]

Hi, 
I have tested this as follows:

The calculated column uses the PATH function. The error explcitely says that a child with multiple parents is not supported. 

With a "clean" tree it works:

The big question is what you explicitely know about your graph in advance. If you only have an adjacency matrix or list you are condemmed to graph exploration algorithms. A lot of patterns cover cases, where you know the depth of the graph and can explicitely model what you want.  I asked a similar question to the community some time ago, where I gave my aproach in order to ask if they are easier ways: 

Multiparent Graph, Recursiveness - Microsoft Fabric Community

I got no answer as the text was presumably too long to read 🙂

Best regards 

Christian

[scee07]

Hi, 

if this is a simple tree (every child has exactly one parent, and there are no cycles), then you can use PATH (https://learn.microsoft.com/en-us/dax/path-function-dax). If this hasn't changed, general graph structured are not covered by PATH.
If this is case, I would cross join all nodes and return the path from path. 

If this is not the case, I reverted back to Power Query as Power Query is recursive and DAX is not. There you can implement graph search algorithms like DFS, BFS and then import the result to Power BI, ...

Best regards 

Christian