Dynamic DB Connection Switching Based on Location Slicer in Power BI

[Sujith_00]

Hi everyone!

I have an interesting challenge I'm hoping to get some guidance on. I have a Power BI report currently connected to a SQL Server database. I also maintain an Excel file that contains a mapping of different locations to their corresponding server details (server names, etc.).

What I'm trying to achieve:

1. Add a slicer in my report where users can select different locations
2. When a location is selected, I want the report to automatically switch its connection to the corresponding server/database for that location
3. Important note: All these databases have identical schemas, just different data for different locations

Additionally, I'd like to understand:

• How would this solution work in Power BI Service after publication?
• Would there be any gateway configuration requirements?

Is this kind of dynamic connection switching possible in Power BI? If yes, what would be the best approach to implement this?

Thank You.

[parry2k · Accepted solution]

@Sujith_00 Dynamic db connection is not an issue, the only challenge here is that the connection needs to switch based on the selection made by the user. The only thing I can think of is to use dynamic M parameters which will pass the location to PQ and then further logic will change the connection. Read more about dynamic M parameters here Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

The only other way or a more scalable way I can think of, combine data from all the sources (SQL databases) and add a location column to the data, and based on the slicer, it will filter data for the specific location. This is a more practical solution. But it also depends on the size of data if you have to combine it form the all the SQL servers.