topic Re: Obtain highest level parent recursively in DAX Commands and Tips

Obtain highest level parent recursively

psoundararajan — Thu, 21 Aug 2025 14:44:47 GMT

Hello,

We have a table of relations, and each item has different levels of parent-child relations.

Input data:

Item	Parent
ID1	P1
ID2	P2
ID3	P2
ID4	P3
P1	S1
P1	S2
P2	S2
P3	S3
P3	S4
S1	S1
S2	S2
S3	S3
S4	S4

How do I obtain the following output from above?

Item	Parent
ID1	S1
ID1	S2
ID2	S2
ID3	S2
ID4	S3
ID4	S4
P1	S1
P1	S2
P2	S
P3	S3
P3	S4
S1	S1
S2	S2
S3	S3
S4	S4

An item can be linked to more than one parent. In the target table, I would like to have one row per item-ultimate parent combination. Thanks in advance.

Re: Obtain highest level parent recursively

wardy912 — Thu, 21 Aug 2025 15:31:55 GMT

Hi psoundararajan

In power query, add a new blank query and add the following (edit accordingly)

let Source = YourTableName, // Replace with your actual table name GetUltimateParents = (item as text) as list => let DirectParents = List.SelectRows(Source, each [Item] = item)[Parent], UltimateParents = List.Combine(List.Transform(DirectParents, each if _ = item then {_} else GetUltimateParents(_) )) in List.Distinct(UltimateParents), Result = Table.AddColumn(Source, "UltimateParents", each GetUltimateParents([Item])), Expanded = Table.ExpandListColumn(Result, "UltimateParents"), Renamed = Table.RenameColumns(Expanded, {{"UltimateParents", "Parent"}}) in Renamed

I hope this helps, please mark as solved if it does, thanks!

Re: Obtain highest level parent recursively

Shahid12523 — Fri, 22 Aug 2025 02:47:56 GMT

Load your Item-Parent table.

Create a function that, given an Item, recursively looks up its Parent until reaching the top (where parent = itself).

Expand results so you keep all combinations.

let
Source = YourTable,

// Recursive function
GetParents = (child as text) as table =>
let
DirectParents = Table.SelectRows(Source, each [Item] = child),
WithRecursive = Table.ExpandTableColumn(
Table.AddColumn(DirectParents, "UltimateParent", each GetParents([Parent])),
"UltimateParent", {"Parent"}
)
in
if Table.IsEmpty(DirectParents)
then #table({"Item","Parent"}, {{child, child}})
else WithRecursive,

// Apply recursion to all Items
DistinctItems = List.Distinct(Source[Item]),
Results = Table.Combine(List.Transform(DistinctItems, each GetParents(_)))
in
Results

Re: Obtain highest level parent recursively

v-kpoloju-msft — Fri, 22 Aug 2025 09:36:15 GMT

Hi psoundararajan,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to Shahid12523, wardy912, for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member Shahid12523, wardy912,addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

Re: Obtain highest level parent recursively

raju8004212 — Sat, 23 Aug 2025 10:07:32 GMT

psoundararajan

Hierarchy =
VAR Base = ADDCOLUMNS ( Relations, "OriginalItem", Relations[Item] )
RETURN
GENERATE (
Base,
PATH ( Base[Item], Base[Parent] )
)

try this and let me know if it works

Re: Obtain highest level parent recursively

v-kpoloju-msft — Mon, 25 Aug 2025 05:16:32 GMT

Hi psoundararajan,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

Re: Obtain highest level parent recursively

psoundararajan — Mon, 25 Aug 2025 08:09:13 GMT

Hello, thanks for your responses. I'm trying these at the moment. Please allow me some time to get back.

Re: Obtain highest level parent recursively

psoundararajan — Mon, 25 Aug 2025 08:10:51 GMT

Hello, should this also work if my source table is a summarised table? I'm unable to replace YourTable with the summarized table name.

Re: Obtain highest level parent recursively

psoundararajan — Wed, 27 Aug 2025 14:44:48 GMT

Hello wardy912 , should this also work if my source table is a summarised table? I'm unable to replace YourTable with the summarized table name.

Re: Obtain highest level parent recursively

techies — Wed, 27 Aug 2025 20:19:21 GMT

Hi psoundararajan in power query, add a new blank query > advanced editor, add this, rename it as - GetUltimateParents

(child as text, tbl as table) as table =>
let
// Direct parents of the child
DirectParents = Table.SelectRows(tbl, each [Item] = child),

Result =
if Table.IsEmpty(DirectParents) then
#table({"Item","Parent"}, {})
else
Table.Combine(
List.Transform(
DirectParents[Parent],
(p) =>
if p = child then
// Stop if parent = child (self-loop)
#table({"Item","Parent"}, {{child, p}})
else
let
Sub = @GetUltimateParents(p, tbl),
Output =
if Table.IsEmpty(Sub) then
#table({"Item","Parent"}, {{child, p}})
else
Table.TransformColumns(Sub, {{"Item", each child, type text}})
in
Output
)
)
in
Table.Distinct(Result)

Next create another blank query, add this

let
Source = relations, // replace with your input table’s query name
DistinctItems = List.Distinct(Source[Item]),
Expanded = Table.Combine(
List.Transform(DistinctItems, each GetUltimateParents(_, Source))
)
in
Expanded

Re: Obtain highest level parent recursively

v-kpoloju-msft — Thu, 28 Aug 2025 04:29:09 GMT

Hi psoundararajan,

Is solution provided by the community member techies resolve your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

Re: Obtain highest level parent recursively

v-kpoloju-msft — Sun, 31 Aug 2025 02:48:57 GMT

Hi psoundararajan,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

Re: Obtain highest level parent recursively

v-kpoloju-msft — Wed, 03 Sep 2025 04:03:48 GMT

Hi psoundararajan,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

Re: Obtain highest level parent recursively

psoundararajan — Wed, 03 Sep 2025 06:55:17 GMT

Hey, no. It is not working. I asked a question below, but received no response. The only way I could make it work (for now) is to duplicate the data source and do self-joins.

Re: Obtain highest level parent recursively

v-kpoloju-msft — Mon, 08 Sep 2025 12:16:31 GMT

Hi psoundararajan,
Thank you for the follow-up question.

Attached file gives you the expected results using M query. If your source table is a summarized table (for example, aggregated counts or grouped data), then the script I provided will not work directly because it expects a flat list of item-parent relationships.

If you still have any other questions, please share us the sample input file along with expected output.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

Re: Obtain highest level parent recursively

v-kpoloju-msft — Thu, 11 Sep 2025 04:03:11 GMT

Hi psoundararajan,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

Re: Obtain highest level parent recursively

v-kpoloju-msft — Sun, 14 Sep 2025 15:25:36 GMT

Hi psoundararajan,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

Re: Obtain highest level parent recursively

v-kpoloju-msft — Wed, 17 Sep 2025 03:56:18 GMT

Thank you.