Dynamic RLS for organisational hierarchy using path for multiple managers

[Charan77]

I have users table where I'm using EmployeeID and ManagerID column to create a hierarchy using path function. This user table is connected to Sales Rep table using EmployeeID. This setup works fine in an ideal world but there are few Sales Reps with multiple managers, so path function will not work for these records and the sales data is automatically rolling up to the manager defined by the path function. But I want to be able to handle this situation where sales rep's data will roll up to multiple managers. But I know this should be defined in the model somehow. Right now I'm stuck after creating path for single parent and child hierarchy and dynamic RLS for the same. I'm open for suggestions, thank you!

[v-venuppu]

Hi @Charan77 ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further help.

Thank you.

[v-venuppu]

Hi @Charan77 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @Charan77 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[Charan77]

Thank you for your reply but I'm more concerned about the logic aspect of how to roll up the sales data of sales rep to multiple managers. Right now I'm able to roll up only to one manager using the path function. Can you please share your insights for this scenario? 

Also coming to RLS, the requirement is to filter the report based on hierarchy and not on a special role. For example, if a manager logs into the report, he or she should have access to his or her's data along with their subordinates data. Thanks! 

[lbendlin]

Yes, that's the gist of the discussion here.  You can go the hard route, trying to implement that logic with static RLS and multiple roles, or you could go the simple route with dynamic RLS and a precomputed access matrix.

This may help: Managing hierarchical organizations in Power BI security roles - SQLBI

[lbendlin]

Dynamic RLS doesn't care about hierarchies.  You have a list of USERPRINCIPALNAME() entries, a single role, and an externally maintained mapping/reference table that explains what each of these users can see.

Hierarchies are used with Static RLS and roles etc.