JOIN DISTINCT COLLECT *DESCENDANTS*

melimob

Hi, I am used to using this formula: =JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), <>"")), CHAR(10))

to roll up unique children but have replaced the 'children' with 'descendants':

=JOIN(DISTINCT(COLLECT(DESCENDANTS(), DESCENDANTS(), <>"Total")), CHAR(10))

but it's returning both children and descendants?

I only want descendants?

Any ideas on how to solve welcome. thank you!

SoS | Dan Palenchar

Hello @melimob,

What do you consider to be a descendant that is not a child?

If you want to collect only a specific hierarchy level I would make a helper column with a formula that outputs some value based on a hierarchy evaluation. You could then collect based on that value.

For example, if you have three levels of hierarchy:

1. Grandparent
2. Parent
3. Children

And you only want to pull in level 2 (Parents). You could make a new column with formula =COUNT(ANCESTORS()). This will return 1 for the 2nd level. In your formula you can then use:

=JOIN(DISTINCT(COLLECT(DESCENDANTS(), DESCENDANTS(), <>"Total", [Helper Column]:[Helper Column], 1)), CHAR(10))