Forum Discussion
Change the SQL Database Name of a Data Source based on User ID
I have 50 distributors (sellers of our products). Each distributor has as [Company ID].
Each distributor has their own database on the same SQL Server. The database structures are the same.
What I would like to do is to have Power BI recognize the user and dynamically choose the correct database name for that user.
For example, if I have the following:
if [userID] = [Distributor1] ,
then the data source is [Database1] (set server name = "mrl" and database name = Database1)
if [userID] = [Distributor2] ,
then the data source is [Database2] (set server name = "mrl" and database name = Database2)
...and so on for all 50 distributors.
Is this possible in Power BI?
Thank you for any assistance you can provide.
Tracey.
You might be able to do this if you used direct query and looked at this post by GilbertQ - Power Query (M) – Passing Parameters dynamically to a SQL Server Query - Data Analytics | FourMoo and Power BI
But not sure how performance would be, and what things Direct Query will mess up for you - you cannot use Power BI Time Intelligence.What I would be more inclined to do is create a view on the server that combines all relevant fact tables into one, with a new field that indicates the user name, then use RLS to filter the data at the report level.
3 Replies
You might be able to do this if you used direct query and looked at this post by GilbertQ - Power Query (M) – Passing Parameters dynamically to a SQL Server Query - Data Analytics | FourMoo and Power BI
But not sure how performance would be, and what things Direct Query will mess up for you - you cannot use Power BI Time Intelligence.What I would be more inclined to do is create a view on the server that combines all relevant fact tables into one, with a new field that indicates the user name, then use RLS to filter the data at the report level.
