Recursive query for a tree

[River]

Hi Friends,

We need to build a recursive query for our mangement hierarchy, and it's dynamic,  when given a node/person(not necessary the top person), we need to find out all subordinators under the person.

How can we achieve it?

Many thanks.

[v-stephen-msft · Accepted solution]

Hi @River ,

Try measure like

Measure = 
var _flag=IF(HASONEFILTER('Table'[Level 4]),4,IF(HASONEFILTER('Table'[Level 3]),3, IF(HASONEFILTER('Table'[Level 2]),2,IF(HASONEFILTER('Table'[Level 1]),1))))
var _v=UNION(VALUES('Table'[Level 1]),VALUES('Table'[Level 2]),VALUES('Table'[Level 3]),VALUES('Table'[Level 4]))
return IF(ISFILTERED('Table'),IF(MAX('Table 2'[Column])>_flag && MAX('Table 2'[Level 1]) in _v ,1,0)
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Please see this article on how to use the PATH function to generate the management chain for each employee.  You can then write DAX measures to dynamically show direct reports.

On the Right PATH() - A DAX/Power Query approach to working with parent/child hierarchies. | P3 Adap...

Pat

[Vera_33]

Hi @River 

Can you provide some sample data? For hierarchy, you can probably use List.Generate in M or have a look at PATH in DAX

[River]

Hi Vera,

The hierarchy data is no different than any other company's management hierarchy. Can you provide a sample to do it?

Thanks,