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
lrao
Regular Visitor

RLS in PowerBI using Scripting

Hi All,

I am trying to apply a row level security for a model having Employee -> Manager relationship. The report is a directquery to Oracle db due to sensitive data and security concern. Since, we don't have Oracle 12 client, as a workaround connection to Oracle is via linked server within sql server 2012.

 

I was able to succeed in demonstrating RLS and test it on web by following the steps mentioned in below link
https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

As per my understanding creation and updation of the RLS can be done only through Power BI Desktop and not through scripting.
Creating a role for each Manager in a bigger organization becomes tedious task. Increases maintenance cost due to manual work of handling the roles once it is moved to production.

 

Please help me if there is any way to handle this RLS maintenance using some script within power bi.

Thanks & Regards,
Lokesh

1 ACCEPTED SOLUTION
ankitpatira
Community Champion
Community Champion

@lrao In addition to what smoupre said you can't script out RLS task in power bi desktop but you can achieve somewhat automation (less manual work) by just creating one role and passing UPN property of the logged in user via that role to the user security table where you will have Employee Manager relationship mapped. Check out below link on using Username() function of DAX for RLS.

 

https://powerbi.microsoft.com/en-us/blog/using-username-in-dax-with-row-level-security/

View solution in original post

3 REPLIES 3
ankitpatira
Community Champion
Community Champion

@lrao In addition to what smoupre said you can't script out RLS task in power bi desktop but you can achieve somewhat automation (less manual work) by just creating one role and passing UPN property of the logged in user via that role to the user security table where you will have Employee Manager relationship mapped. Check out below link on using Username() function of DAX for RLS.

 

https://powerbi.microsoft.com/en-us/blog/using-username-in-dax-with-row-level-security/

@Greg_Deckler - Thanks for the input.

 

@ankitpatira - Many Thanks for the solution. Other day I was trying this on SSAS Tabular model role and it didn't work there. But in PowerBI this solution has worked as expected. Smiley Happy

Greg_Deckler
Super User
Super User

To my knowledge, there is no ability to script or automate things in the Desktop.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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