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
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:
Warm regards
Jakub Dušek
Solved! Go to 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
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
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:
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
Hello @v-lili6-msft,
thank you for this information.
What about the other questions?
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.