I’m trying to generate a text string of all related items of a record/node. Similar to what a path does, but a path keeps its hierarchy while in this case this isn’t possible as their could be “loops” and I’m not that interested in the actual hierarchy itself reflected in the generated string.
So for instance the following:
From
To
Result
A
A-B-C-D-E-F
B
A-B-C-D-E-F
C
A-B-C-D-E-F
D
A-B-C-D-E-F
E
A-B-C-D-E-F
A
B
A-B-C-D-E-F
B
C
A-B-C-D-E-F
D
E
A-B-C-D-E-F
F
A
A-B-C-D-E-F
G
G-H-I
H
G-H-I
I
G-H-I
G
H
G-H-I
H
I
G-H-I
J
J
K
K
In the attached Power BI file you will find sample data and nodes visualizing the relationship between records. The reason why I’m looking for the generated string, is so that you can filter a specific item and see the whole chain of this item. So if you have other ideas to get to the same result, that would be fine of course.
Below an example of a loop in the Power BI file, but I want to be able to pass on any item within this chain and see the whole chain (so all related items, directly & indirectly).
I’ve changed the column names, perhaps this is easier? So similar to path, I’m looking for the hierarchy but as I have circular dependencies, path cannot be used. I’m looking for something that can simply concatenated all related (unique) items.
The point is, all of these are relationships towards each other. I can display this nicely in a network graph, a custom one based on the Social Netwok Graph (forked from its code), but what I’m mostly interested in, is the filter. So that I can see the whole cluster for example where “A” fits in, or “E” (the same), …
Potentially, a dirty solution would be a way to create a function that loops a predefined set of amount of times. For example: something that lookups all the parents, then the parents of the parents, … for X amount of times and then a concatenation of all unique items.
Best of course would be that it stops when it finds a value that it already found earlier.
As a workaround, would it be possible to have a function lookup the parents. The result should be a table as there could be multiple. Then you run that same function again based on expanded table of the previous results. And this up to X times (let’s say 5). The final result should then concatenate all the results and you should have something similar?
Just don’t know exactly how to do this in a function.
That would be a nice twist to her solution. I’ve tried to tweak her solution to flatten hierachies as well, but got to a dead end.
I’m going to test this one in the next days, but bear with me, need to set up the old model again. You may have seen in the DAX thread that I’ve received a brilliant solution for non-multiple-parent scenario. The result of this in a custom network graph we’ve build is brilliant. (Will be embedded in D365 forms).
