Forum Discussion
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.
Hey Anonymous
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!
5 Replies
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 Anonymous
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.
Has this been posted as an enhancement option anywhere?
