Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGlad I could help @ninsights
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
61 | |
19 | |
17 | |
13 |