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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
jdusek92
Helper IV
Helper IV

RLS - Analyze in Excel connection

 

Hello,

I have successfully set up row level security using this Table filter DAX expression:

SEARCH( userprincipalname(), [Email], 1, 0 ) > 0

 

I am now experimenting with "analyze in Excel" feature. I would like to use the RLS to load RLS filtered table (NOT to actually analyze the OLAP Cube with pivot table)

After creating a dummy Measure=1 I was able to double click ("drill through") the Pivot table (by double clicking on the measure value in the pivot table). This gave me a linked table with the following command text:

DRILLTHROUGH MAXROWS 1000
SELECT FROM [Model] WHERE
(([Measures].[Measure],[Table1].[email].&[xxx@xxx.xxx],[Table1].[ID].&[4],[Table1].[X].&[654]))

 

 

After removing the filter part =>

DRILLTHROUGH MAXROWS 1000
SELECT FROM [Model]
WHERE (([Measures].[Measure]))

=> I was able to get a linked table with all the columns and rows. Also the RLS works: when I refresh from a different account I see only the allowed/limited rows. - Amazing! (Using Office 365, signed-in account)

 

My goal was to set up a linked EXCEL table with working RLS (without using MS SQL server or other) - this seems to work now!

My question is:

  1. Will the connection obtained via "Analyze in Excel" be working PERMANENTLY or is it only TEMPORARY?
  2. Can I modify the "SQL" command text even more to return the whole table/data set? Something like "Select * From ... " - (of course while respecting the RLS)
  3. Is this approach reliable? Can you think of any downsides?
  4. Could someone exploit the connection to ignore the RLS?

 

Warm regards

Jakub Dušek

1 ACCEPTED SOLUTION

hi , @jdusek92

1. It is PERMANENTLY, You can use it next time you open it, also it will verify your account.

3. reliable, although I can't find the relevant documents for you, Unreliable Microsoft won't use it.

4. Impossible, Security has always been a major concern for Microsoft.

 

Best Regards,

Lin

Community Support Team _ Lin
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

6 REPLIES 6
v-lili6-msft
Community Support
Community Support

HI, @jdusek92

Excel is Connected Live to the Power BI Model in the Service.

and current RLS filter effect only works for read permission users, if you are dataset owner or you have edit permission, then RLS not works.

 

http://radacad.com/power-bi-and-excel-more-than-just-an-integration

https://community.powerbi.com/t5/Service/RLS-Analyze-in-Excel/td-p/222826

 

Best Regards,

Lin

 

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

Hello @v-lili6-msft thank you for your reply. 

what you say is completely clear to me and is not a part of my question - RLS works as expected - owner sees all and other users assigned to RLS role sees filtered data.

 

My question is about thr linked data table obtained via drilling through OLAP pivot table (created by Analyze in Excel)

 

Warm regards

Jakub

This is how I get the linked table I have questions about:

gif2.gif

hi, @jdusek92

thr linked data table obtained via drilling through OLAP pivot table (created by Analyze in Excel) is couldn't achieve like "Select * From ... ". for you need to sign in Office 365 account and it also will be verified by RLS.

 

Best Regards,

Lin

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

Hello @v-lili6-msft,

thank you for this information.

 

What about the other questions?

 

  1. Will the connection obtained via "Analyze in Excel" be working PERMANENTLY or is it only TEMPORARY?
  2. Can I modify the "SQL" command text even more to return the whole table/data set? Something like "Select * From ... " - (of course while respecting the RLS)
  3. Is this approach reliable? Can you think of any downsides?
  4. Could someone exploit the connection to ignore the RLS?

 

I want to use this linked data table to create personalised reports in Excel - users will refresh the connection themselves and are supposed to get only "their" rows. (Power BI solution is also planned for later). Therefore I need the connection of this linked data table to be permanent and to ensure RLS filtering.

 

 

Warm regards,

Jakub

hi , @jdusek92

1. It is PERMANENTLY, You can use it next time you open it, also it will verify your account.

3. reliable, although I can't find the relevant documents for you, Unreliable Microsoft won't use it.

4. Impossible, Security has always been a major concern for Microsoft.

 

Best Regards,

Lin

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors
Top Kudoed Authors