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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tanayraj
Frequent Visitor

How to do Dynamic row level sercuity on datamart?

Hello,
I have created a datamart on the Power BI service.  Now I want to create a report and implement dynamic RLS. So, On the Power BI desktop, I chose Datamart as my data source. Data is loaded. But now, the "Manage role" is not available. It is grey.  It is not clickable. I tried to import datamart locally but it did not solve my problem. After that, I change my data source from Datamart to the Sementic model of that Datamart. But the "manage role" is still inaccessible. 

Then I realized that it was not possible to implement a managing role in the Sementic model. Online and Microsoft docs suggest the implement RLS security on the Sementic model. 

How is that possible? How to do? 


Still, I tried RLS on datamart. On Datamart, it only allows static RLS security. There is no DAX editor where I can write DAX code to implement dynamic RLS. (Check the Screenshot attached to this post.) I want to use the USERPRINCIPALNAME() function but without the Dax editor, I can't do it. 

Please share any kind of information regarding this. 

Thank you.

Tanayraj_0-1713368263031.png

 

1 ACCEPTED SOLUTION

Hey @v-zhouwen-msft 
Thank you for your response. I understood what you mean. And I also tried what you suggested. I imported data as an SQL endpoint and after that, I could see the "Manager role" on the PowerBI desktop. 

But this doesn't entirely solve my problem. Because I am trying to implement RLS on datamart (on data itself). If I implement RLS on the PowerBI desktop, I have to upload a sementic model/report on a workspace. With this, I can't achieve my goal. 

It would be nice If I could implement dynamic RLS, especially USERNAME() or USERPRINCIPALNAME() on Datamart. With the SQL endpoint, I am adding one extra layer/ step in my architecture. 

Thank you!


View solution in original post

5 REPLIES 5
nrichards_au
Frequent Visitor

Has this been posted as an enhancement option anywhere?

v-zhouwen-msft
Community Support
Community Support

Hi @Tanayraj ,

Regarding your question, roles and rules can only be defined in Desktop if the connection mode is in Import or Direct Query.You then mentioned that you are configuring RLS in the datamart, which is a step in the right direction.The configured RLS on datamarts automatically gets applied to downstream items, including the auto-generated semantic models and reports.Datamarts use the enhanced row-level security editor, which means that not all row-level security filters supported in Power BI can be defined. Limitations include expressions that today can only be defined using DAX including dynamic rules such as USERNAME() or USERPRINCIPALNAME(). 

Control access to datamarts (preview) - Power BI | Microsoft Learn

 

I looked up the documentation and one of them may be the solution. If Datamart's RLS functionality is not flexible enough, you can use Datamart's SQL endpoint to create custom datasets.

powerbi - RLS in datamarts - where is the DAX option? - Stack Overflow

 


Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hey @v-zhouwen-msft 
Thank you for your response. I understood what you mean. And I also tried what you suggested. I imported data as an SQL endpoint and after that, I could see the "Manager role" on the PowerBI desktop. 

But this doesn't entirely solve my problem. Because I am trying to implement RLS on datamart (on data itself). If I implement RLS on the PowerBI desktop, I have to upload a sementic model/report on a workspace. With this, I can't achieve my goal. 

It would be nice If I could implement dynamic RLS, especially USERNAME() or USERPRINCIPALNAME() on Datamart. With the SQL endpoint, I am adding one extra layer/ step in my architecture. 

Thank you!


Hi @Tanayraj

Regarding your question, there is also no option to use DAX in the screenshots of the official documentation, and I have searched for that question before and I didn't see any articles where you can create an Dynamic RLS in the datamart, which only supports a static RLS.Perhaps the feature will go live at some point in the future.

vzhouwenmsft_0-1713432825345.png

 

Thats Bummer!!!

But Thank you. I will accept this as a Solution. At least I have some kind of answer. 

Thank you for your time! @v-zhouwen-msft 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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