Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
ninsights
Helper III
Helper III

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.

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

Glad I could help @ninsights 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you @edhans for this information and letting me know that time intelligence won't work under that scenario. That's very important for this dashboard so I will ask IT to create the view you suggested that I can use with RLS. I greatly appreciate your response. 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.