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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Khristian
Advocate II
Advocate II

RLS Analyze in Excel

Hi everyone!

 

Into the PBI documentation, the RLS rules applied into powerbi desktop should run in Analyze in Excel too, but it does not....

 

Any one of you have the same problem?

 

2 ACCEPTED SOLUTIONS

Hi @nbailey,

 

We can define the roles in Power BI desktop, but after publishing to the service, we need also add members under this role to take effect for this user. We can also use Test as role to check results. I would suggest you to take a look at this article: Row-level security (RLS) with Power BI (Preview).

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

i did this.

 

 

Well, i faced the samething... now it works....

 

well, i create my model into powerbidesktop feed it from sql data base....

 

Then a RLS is created and tested into the powerbi desktop too..

 

If it work....

 

A Group is created into powerbi service

 

the model is published into a GROUP ,  this group is set "Member can only view powerbi content"

 

You be sure the user´s you add into that group has a powerpro profile

 

So try into powerbi publisher for excel, and works....

View solution in original post

16 REPLIES 16
patrickoliver
Frequent Visitor

We're having a similar issue. We have RLS set up as a role against one of our tables but, because Analyze to Excel exposes the whole data model behind the report to the user, they can see other data if they choose to exclude the table that has the RLS on it from their PowerPivot reporting.

i´m pettry sure, RLS does not applied in powerpivot models, you most change the model into powerbi desktop

 

Regards

Our model is against an on premise SQL Server database. The problem is that whatever the data source is and however much of it is exposed in the Power BI report, you can get to all of the data that was in the Power BI Desktop model through the Analyze in Excel functionality.

Well, i faced the samething... now it works....

 

well, i create my model into powerbidesktop feed it from sql data base....

 

Then a RLS is created and tested into the powerbi desktop too..

 

If it work....

 

A Group is created into powerbi service

 

the model is published into a GROUP ,  this group is set "Member can only view powerbi content"

 

You be sure the user´s you add into that group has a powerpro profile

 

So try into powerbi publisher for excel, and works....

I tried publishing to a Group Workspace as you suggested (with the view only setting). The user (who has a trial Power Pro licence) could see all of the underlying data using "Analyze in Excel" as long as they avoided the table that has the RLS roles against it.

Well i guess you didn´t get the perfomance you wanted, so, the RLS should be tested....

I worked out what I was doing wrong in my testing. I'm happy that the RLS restrictions are being passed correctly through the Analyze in Excel functionality.

Anonymous
Not applicable

Please, How did you solve it?

I have exactly the same problem.

Thanks!

i did this.

 

 

Well, i faced the samething... now it works....

 

well, i create my model into powerbidesktop feed it from sql data base....

 

Then a RLS is created and tested into the powerbi desktop too..

 

If it work....

 

A Group is created into powerbi service

 

the model is published into a GROUP ,  this group is set "Member can only view powerbi content"

 

You be sure the user´s you add into that group has a powerpro profile

 

So try into powerbi publisher for excel, and works....

v-qiuyu-msft
Community Support
Community Support

@Khristian @nbailey In Power BI Service, the RLS role has some limitations about role member:

 

1. RLS is not applied to the dataset owner.

2. If in a Power BI group workspace, the roles will be applied to read-only members.

 

If you are the dataset owner and you add yourself under this role, you can run the report to check you can also see complete data in the dataset, as you are the dataset owner. So it's expected that after use Analyze in Excel feature to open the .odc file, you can also see complete data.

 

In your scenario, you can add a member who is not the dataset owner under role. And in My Workspace, you can pin the report visual to a dashboard and share this dashboard with this member. Then you can get .odc file via Analyze in Excel, then share this file with this user. You will find the user cann't see complete data.

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft, i have al ready submmited a ticket....

 

Tks, but i did the things you described.... but it does not work

Hi @Khristian,

 

Based on my test, the RLS is also working in Analyze in Excel feature. Please check if all steps to define RLS are the same as mentioned in this article: Row-level security (RLS) with Power BI (Preview).

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for the clarification (when RLS was managed in PowerBI.com previously - before it was brought into be managed in Desktop, it was possible to put myself in different roles to test visuals, so this is different behavior now).  So the only place we now have the ability to "test" roles is in the PowerBI Desktop, which means we don't have the ability to test any content created on PowerBI.com.  This will make it more difficult to troubleshoot end user issues (or validated security) on PowerBI.com content.

Hi @nbailey,

 

We can define the roles in Power BI desktop, but after publishing to the service, we need also add members under this role to take effect for this user. We can also use Test as role to check results. I would suggest you to take a look at this article: Row-level security (RLS) with Power BI (Preview).

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nbailey
Advocate I
Advocate I

I tried to test the RLS in PowerBI and in Analyze in Excel by putting myself in one of the roles, but it did not restrict the data in either case for me.

I have the same behavior!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors