Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Row Level Security in an Org Chart

Hello.

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

ManagerPersonAssociate IDBonus
Steve PMitch M1235

$50

Steve PJustin S1236$60
Justin SAlexey A1432$50
SalSteve P1564$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!

1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

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

 

View solution in original post

5 REPLIES 5
PC2790
Community Champion
Community Champion

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
Not applicable

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
Community Champion
Community Champion

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
Not applicable

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
Community Champion
Community Champion

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors