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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Switch dynamically between the databases depending on user credentials

Hello everyone,
I have 2 databases in SQL Server with exact same schema. I have a report in Power BI that will be shared online with 2 users (with power BI licenses). Each database relates to a user. How can I make the report switch dynamically between the databases depending on which user is logged in?
I've explored RLS but it is not feasible to combine tables from the two databases since they have millions of rows. I would prefer to keep them independent.  I've also explored the option of using parameters that defines the database name  but that still implies manual action of switching to the relevant parameter for each data source.
Any ideas would be much appreciated.
 
Adrian
4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

Why not create two separate reports based on these two databases? For example, if you have databases ds1 and ds2, you can first connect to ds1 to create report r1, then copy the r1 report file, just change the data source information (because you mentioned that the two data sources have the same schema) and then save it as r2. Then upload reports r1 and r2 to the service and share them to user 1 and user 2 respectively.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yiruan-msft,
I would like to have only one report because the number of users will increase overtime and having a duplicate report+data source is not feasible. I guess this is not something that can be done in power bi but perhaps azure?

Anyway thank you for looking at this.

Best

Adrian

Anonymous
Not applicable

Hi @Daryl-Lynch-Bzy 
The users are not technical. They are just accessing/reading the report and the reason I'm looking for a dynamic solution is because the number of users will increase over time, thus need to look for a scalable solution where I don't have to duplicate the same report for each user. Is there any other way I can look at this?

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @Anonymous , I don't think this is possible.  Power BI would be able to select between the two databases based on current user.  This sounds like a Direct Query scenario.  Please consider the following:

  • If there are only two users, why not just give them access to the Server SQL directly.  They can fill their boots and even use Power BI if they want to.
  • Consider creating a Template that you can publish to respective user and have them enter the Connection Details and credentials to access the Data Model.

Otherwise, you need manage 2 workspaces, 2 datasets and 2 reports.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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