Forum Discussion

River's avatar
River
Helper IV
5 years ago
Solved

Recursive query for a tree

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.

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      River
      Helper IV
      5 years ago

      Hi Vera,

       

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

       

      Thanks,

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.