Dynamic DB name and Server for SQL Server data source

[DAPBI]

Hello everyone,

An absolute newbie in Power BI so appreciate your help and support on this. 

I have SQL Server as a data source.

I am using the below approach and will also explain the requirement so if anything has to be done differently do let me know.

1. I used the Connect -> Sql Server option on the top menu

2. Provided the Db name and Server

3. Selected the "Using DirectQuery" option

4. In Advanced Options provided an SQL statement - For now - Select * from tblName.

5. Click on Load button 

This shows the list of fields on the right.

I dragged a chart control on the Report and the required field from the field list. The chart correctly shows the data from the selected DB, Server and SQL query.

But my real challenge and the requirement is to have the DB name and Server dynamic for the Report.

Meaning - I need to have two dropdowns on the Report page - one showing the list of DB names and the other showing list of Server names. I am using the dropdown option of a Slicer control here.

When the user selects the Db and Server from the dropdown, the chart should reflect based on the data from that selected DB and Server. The SQL query is always the same.

Could you please assist on this.

Thanks in advance.

[amitchandak]

@DAPBI , refer if this can help

https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters

[DAPBI]

Hello @amitchandak 

Thank you for your reply.

I have gone through the entire link. I was aware of the parameters. But it is not user-friendly to always go to the Parameters and edit them. 

I already have two dropdowns created out of a Slicer control - 1 shows the Db name and 1 shows the Server.

Is there a way that when a user selects the Db and Server from the dropdowns and the Charts below the dropdowns automically refreshes to the data from that particular Db and Server?

Thanks.