Forum Discussion

Tanayraj's avatar
Tanayraj
Frequent Visitor
2 years ago
Solved

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.

 

Need Help
Show and Tell
  • Tanayraj's avatar
    Tanayraj
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

    • Tanayraj's avatar
      Tanayraj
      Frequent Visitor
      2 years ago

      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!


      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.