Dynamically change Database Endpoint

[csaethre19]

I am making reports that need to be updated live so I am using Direct Query mode via SQL statements to create these. 

We have two versions of our database, there is one for research and one for production. These databases have the same structure but contain different data. 

I need to duplicate some of these reports so that it can be used for each database. 

I see in import mode I can copy the .pbix file and just switch the data source through data source settings. However, since I am using Direct Query mode this is not supported. 

Does anyone have any experience with this type of workflow? What is the best approach to dealing with creating and updating reports that need to display data from each database?

[GilbertQ]

Hi @csaethre19 

You could look at using this, where you could put in the SQL server database names and get that to pass through via Parameters?

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

[csaethre19]

Thank you for the suggestion and resource. I went through this step by step and managed to set up a bound parameter for the database URL endpoint and database name to change in the M query dynamically. However, it was not successful as it breaks my visuals and says that it is unable to read beyond the end of the stream. I am using Direct Query mode and I am guessing that this kind of thing is not supported while using Direct Query. 

If you have any suggestions for dynamically changing the database while using Direct Query mode that would be appreciated. 

Thank you!