Changing the datasource

[Byzbkr]

Hello,

I have more than one database in my SQL server. In addition, the tables in the databases are same. I created a report with a specific database. However, I want to give the user the option to change the database of the report they are viewing by giving a dropdown on the visual screen. But I don't want to show the database names as an option to the user. I have a table with names corresponding to database names in a separate sql table. the names written in this table are brought in dropdown and when user is select the name, I want the relevant database to appear. How can this be done? Thanks in advance for your help.

[lbendlin]

Before you invest any more time in that be aware that the Power BI Service and specifically the Power BI gateway does NOT support switching databases.  The difference between SQL.Databases and SQL.Database does only exist in Power BI Desktop.  And we haven't even mentioned the security implications yet - as you would have to give all users full access to all databases on that server to make this work.

[Byzbkr]

Hi, I already using Power BI Destkop and I can give all users full access to all databases. So what should I do for switching databases in visulization-pane?

[lbendlin]

- make sure the data source is in direct query mode 

- enable the preview feature "dynamic M query parameters

- learn about how to set up parameters and tie them to filters 

- modify your M to use custom SQL and include the parameter 

Once again - think long and hard before implementing this.

[Byzbkr]

Thanks for your help. But main problem I can't find a way to modify M query. Is there a similar M query you can share?

[lbendlin]

If you have Power BI Desktop then you have access to the Power Query part as well. Click this icon on the top menu 

Frankly I would not do something like what you are planning to do (for the reasons I mentioned earlier) and I wouldn't want to give you bad advice by sharing the code.