Allow Users to Dynamically Change Data Source

[KamrynWurth]

Is it possible for users to select what server they want as their data source?

Background: I am working on a Power BI report that uses Direct Query only. I want the user to be able to dynamically switch what server/data source the data is coming from and the report to refresh automatically. I want them to be able to do so from the Power BI service, so possibly using a slicer.  The database and table structures are consistent across servers. Some of the servers will be using a cloud connection and some will be using a gateway. There are too many records returned by each server to append them into one table and then filter by server.

What I have tried:

I created a parameter in the Power Query Editor and added the parameter to my connection strings in place of a specific server name. I also have a table that contains the list of server names which is bound to the parameter. In Desktop I can change the parameter value by doing Transform data -> Edit parameters -> type in new server name -> Apply changes. While this method works, it is quite a few steps and would not work for users in Power BI service. When I try to use a slicer for the server name field, the parameter value is not updated, and the visuals do not refresh with data from the new source.

Is there an additional step I am missing to use a slicer for changing the parameter? Or is there an alternative method for users to dynamically change data sources from Power BI Service?

[AnaMariaDe]

Hello @KamrynWurth! have you solved? I am facing with the same issue, could you kindly help me? Thank you in advance

[KamrynWurth]

Hi @AnaMariaDe. I was not able to make this work as I was hoping. Instead I have created seperate copies of the report that each point at a seperate data source.

[Idrissshatila · Accepted solution]

HEllo @KamrynWurth ,

the one thing I could imagine is loading all data from all servers and have a server slicer that filter data as it is, other than than I can't think of anothr solution.

[KamrynWurth]

Thank you @Idrissshatila, I figured that might be the case. Unfortunately the amount of data that needs to be loaded from each server would not make your idea feasable.