Forum Discussion

ninsights's avatar
ninsights
Icon for Helper III rankHelper III
4 years ago
Solved

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...
Need Help
edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
4 years ago

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.

  • ninsights's avatar
    ninsights
    Icon for Helper III rankHelper III
    4 years ago

    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.