Connection to different environment in PBI Desktop

[Trodo737]

In the system I'm working with, there are three environments(server) for SQL databases, Dev, Test and Prod and the same SQL tables and views in all environments. I've created some reports in PBI Desktop with connection to Dev.

There is no Analysis- or Reporting Services, just PBI Desktop and connection to SQL databases. In the reports I've created in Dev, if I want to connect to Test or Prod environment, the SQL views would be added to the list of views I have already from Dev in my report. How can I connect to other environments without adding SQL views?

[Trodo737 · Accepted solution]

I resolved the issue by adding the data source in Data source setting.

File ->Options and setting -> Data source setting -> and there added other data sources to the list and it works to select other DB servers.

[Thejeswar]

Hi @Trodo737 ,

You can use parameters to connect to different environments in the same report. The Below are the steps you can take to connect using parameters

1. Create Parameters:

   • Open Power BI Desktop and go to the Home tab.
   • Click on Manage Parameters and select New Parameter.
   • Name your parameter (e.g., “ServerName”) and set the type to Text.
   • Add the possible values for your parameter (e.g., different server names) and set the default value.

2. Use Parameters in Queries:

   • Go to the Home tab and select Transform Data to open the Power Query Editor.
   • In the Power Query Editor, select the query you want to parameterize.
   • Click on the Advanced Editor and replace the static parts of your connection string with the parameters you created. For example:

     let
         Source = Sql.Database(ServerName, DatabaseName)
     in
         Source

3. Apply Parameters:

   • After setting up your parameters, go back to the Home tab in Power BI Desktop.
   • Click on Transform Data > Data Source Settings.
   • Select the data source you want to parameterize and click on Change Source.
   • Replace the static values with the parameters you created.

You can try the above steps to setup connection using parameters.

You can then use the parameter in slicer and try changing the value and see if it works with new connection

Regards,

[Trodo737]

Thank you @Thejeswar . I'm using the new version of PBI Desktop and it seems that the new one doesn't have this option anymore(for creating parameters for datasources https://www.phdata.io/blog/how-to-parameterize-data-sources-power-bi/). I found 'New parameters' under 'Modeling' but there is only option for numeric and field.