Forum Discussion

lherbert501's avatar
lherbert501
Post Prodigy
2 years ago

Row Level Security vs Parameter

Hi ,

 

I currently have the same report for 10 different customers and just change a parameter at the top of the SQL for each customer.

 

Can this be done in a more efficient way using Row level Security or similar or would that not be posssible?

 

I'm just trying to find a better way than having to make changes to each PBIX.

 

Thanks in advance

 

Liam

Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    lherbert501 yes you can surely use RLS, load all the data, and apply row-level security based on the logged-in user. There are tons of blog posts that you can google search on how to implement it. it is pretty straightforward.

    • lherbert501's avatar
      lherbert501
      Post Prodigy
      2 years ago

      Hi parry2k, okay thanks. I know RLS can work with users in the same business, but I didn't know if it was the right way to go if its different customers in this case, as they're not logged in users and just access via a URL.

       

      Thanks Liam

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi lherbert501 ,

    There are two ways to solve your problem:

     

    If the report is in Power BI Desktop, you can use Parameter to:

    Manage Parameter for your report

    Export power bi template

    Users can import Power BI Templates in Power BI Desktop

    The template will prompt the user to select from the parameters

    After specifying the parameter value, a new PBIX file will be created that contains the data of the current user

    Deep Dive into Query Parameters and Power BI Templates | Microsoft Power BI Blog | Microsoft Power BI

     

    If you're publishing a report to Power BI Service, you can use RLS:

    Create a user information table in the data model that includes the username and user email address that can access the data

    Establish a relationship between a user information table and a data table

    Create a role in Power BI Desktop that defines filtering for the user information table. Expressions should USERNAME() or USERPRINCIPALNAME()

    Publish the report to Power BI Service and add users to row-level security.

    Finally, share the report with your customers.

     

    Best Regards,

    Nono Chen

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

    • lherbert501's avatar
      lherbert501
      Post Prodigy
      2 years ago

      Hi Anonymous 

       

      Thanks for your reply. As its being published to the gateway and just a URL being created for each report based on their name parameter, could RLS still be used to make this work?

       

      Thanks