Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
Hi Vera,
The hierarchy data is no different than any other company's management hierarchy. Can you provide a sample to do it?
Thanks,