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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
TCavins
Helper V
Helper V

Row Level Security Only on Drillthrough

I have a single dataset that populates a bar chart based on groups. That bar chart then drills through to a details page to see each individual record.

 

How can I set up RLS so it limits only the drill through page to allow users to only see their groups but still see all groups on the main bar chart?

 

Will I need to create duplicate datasets and apply the RLS logic on the drillthrough dataset only?

1 ACCEPTED SOLUTION

My solution:

 

1) Import original dataset, call it XXX

2) In modeling tab, create a new table and set it equal to my original dataset, call it XXX_Secure. (XXX_Secure = XXX)

3) Create a relationship between XXX and XXX_Secure on group name.

4) Configure Row Level Security on the USERPRINCIPALNAME on table Y.

5) Create relationship from table Y to XXX_Security on the group name. 

6) Create bar chart off of XXX

7) Create drill through detail page, include all fields you need from XXX

😎 Also add the group name from XXX_Secure to the detail page and minimize the column so it can't be seen.

 

This will allow all users to see all the data in the bar chart regardless of group name. Every bar in the chart will have a drill through option, but clicking on them will return empty details except for the groups the person viewing the report has access to.

View solution in original post

9 REPLIES 9
Pragati11
Super User
Super User

Hi @TCavins ,

 

RLS is applied at a data-level. It has nothing to do with the tabs on your report.

It will apply filtering to the rows in youe dataset and further on the whole of your report, ir-respective of; if it is your main tab or your drill-through tab.

 

Thanks,

Pragati

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

@Pragati11 Hit the nail on the head. 

 

The only thing I would add is, you can use the "View as" button to test RLS before publishing. This will allow you to test your roles to ensure they are working correctly.  

Hi @Anonymous ,

 

Thanks for endorsing me 🙂

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

My solution:

 

1) Import original dataset, call it XXX

2) In modeling tab, create a new table and set it equal to my original dataset, call it XXX_Secure. (XXX_Secure = XXX)

3) Create a relationship between XXX and XXX_Secure on group name.

4) Configure Row Level Security on the USERPRINCIPALNAME on table Y.

5) Create relationship from table Y to XXX_Security on the group name. 

6) Create bar chart off of XXX

7) Create drill through detail page, include all fields you need from XXX

😎 Also add the group name from XXX_Secure to the detail page and minimize the column so it can't be seen.

 

This will allow all users to see all the data in the bar chart regardless of group name. Every bar in the chart will have a drill through option, but clicking on them will return empty details except for the groups the person viewing the report has access to.

Can you please make the reference more easy to understand. 

I have a similar situation - trying to figure out what is Table Y - for the userprinciplaname().

Thanks again!

It's the table that lists out a record for every login and permission.

Thanks TCavins.

so a table with user name, upn, and Y or N for drill thru permission?

Not a Y/N for permission but actual value. bob@company.com, 'Microsoft' can see all records where column x is Microsoft. 

In your Power BI report, you set up the security on the report Under Modeling > Manage Roles and create a role, select the table that has your security (Table Y) and a DAX expression to say [Username] = USERPRINCIPALNAME().

You then set up your model to say TableY links to Companies(M*M) and Companies is linked to your dataset(1*M). This way, the security table filters the companies table that then filters your dataset.

In my Microsft example, security table will have bob and Microsoft. The companies table will have a list of distinct companies in your dataset. Your dataset should contain the company column for each record.

In the relationships, you set Table Y filters Company table. Company table filters your dataset.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.