Row Level Security in an Org Chart

[Anonymous]

Hello.

I have a table representing all people in an organization, and who they report to. See below:

Manager	Person	Associate ID	Bonus
Steve P	Mitch M	1235	$50
Steve P	Justin S	1236	$60
Justin S	Alexey A	1432	$50
Sal	Steve P	1564	$70

I want to create a way for all managers to see the Bonus field for all of their reports as well as the people who report to their reports. For example, Sal should see all data for everyone because he is at the top of the org chart. Steve should see Justin, Mitch, and Alexey. Justin should see only Alexey. Mitch and Alexey have no reports, so they should only see themselves.

Is there a measure or another table I need to make to add some sort of flag or way to filter this table based on reports? IDEALLY I would like to create a scatterplot where RLS can be applied so that managers can see their reports (and their reports reports) plotted with their bonuses showing.

Thanks!

[PC2790]

And you can make use of "Path" and "PathItem" to get the heirarchy -PATHITEM Function DAX - SqlSkull and then use it to apply dynamic Row Level Security.

Check out these links to implement dynamic RLS:

Power BI – Dynamic Row Level Security – Tips to get it working! - oxcrx34285

Dynamic Row Level Security with Manager Level Access in Power BI - RADACAD

[Anonymous]

Would you mind giving me a sample DAX to use PATHITEM correctly? I appreciate your links and understand conceptually but could use an example. Thanks!

[PC2790]

Here you go.

I have made use of PATHITEM and PATHCONTAINS(in Manage Roles).

Also please have an email column if you want to use UserPrincipalName()

[Anonymous]

Thanks this is helpful! Now how would I create a way to filter a table for all of Steve's reports or Sal's reports in the same field?

For example, how would I use the Hierarchy field to show Steve's reports when I want to create Steve's version, or Sal's reports when creating Sal's version?

[PC2790 · Accepted solution]

I have done that for you already in the sample file.

In modeling---> manage roles ---> create a new role and use the dax as used in the sample file provided above.

See if you are able to do it