Row Level Security vs Parameter

[lherbert501]

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

[Anonymous]

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 B...

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]

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

[parry2k]

@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]

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