Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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,