Dynamic database use

[Anonymous]

Hello all,

I have 2 databases (will become more than 2 in the future). These databases both have a identical table called "Geo" which looks like the following:

[ID][Lon][Lat]

What I would like to achieve is to be able to change database dynamically as input. Being able to select database X or Y from a slicer. When X is selected the [ID][Lon][Lat] from database X are used for the visuals (ESRI MAP), When Y is selected [ID][Lon][Lat] from database Y are used.

A thought I had was to create a dynamic table with [ID][Lon][Lat] with 2 variable sources, Database X and Database Y. Those variables could be selected in the dashboard and this would make it possible to let a user select which data the table should have. I am not sure if this is possible and/or this would be the best solution. 

Any thoughts on how to achieve this would be greatly appreciated 🙂

Best Regards,

L.Meijdam

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

We can’t set dynamic connection to data source based on selected values in a slicer visual. But we can do it via Query Parameter feature.

Based on my test, you can refer to below steps:

1.Create a Parameter in query editor.

2.Get data from the SQL Server database and choose the Parameter.

3.Click “ok” and apply it. Now you select the different database by using the parameter.

Reference: http://biinsight.com/power-bi-desktop-query-parameters-part-1/

Regards,

Daniel He

[Birdjo · Accepted solution]

Hello @Anonymous,

I have similar use cases where I need to get data from 2 SQL Databases containing the same tables.

I know 2 simple ways to achieve what you're looking with SQL Databases

1. Using Native query to unify the the tables and get source database as a column:

SELECT
*
, 'Database1' AS [Database]
FROM Database1.schema.Table

UNION ALL

SELECT
*
, 'Database2' AS [Database]
FROM Database2.schema.Table

2. Getting the 2 tables in power query, adding a custom field with the database (or desired) name as a value and appending them into 1 query:

1. Add a custom column by going to the Add Column tab in the Power Query Editor:

2. Repat for table 2

3. Appending queries 

4. Visualize as you wish:

I hope that solves your issue!

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

We can’t set dynamic connection to data source based on selected values in a slicer visual. But we can do it via Query Parameter feature.

Based on my test, you can refer to below steps:

1.Create a Parameter in query editor.

2.Get data from the SQL Server database and choose the Parameter.

3.Click “ok” and apply it. Now you select the different database by using the parameter.

Reference: http://biinsight.com/power-bi-desktop-query-parameters-part-1/

Regards,

Daniel He

[Anonymous]

Hello @Birdjo,

Thanks for sharing some approaches to solve this problem however I am going to stick with the solution given by @v-danhe-msft, your answers were definitely a good answer to my question and I am sure I can make good use of this knowledge in the near future 🙂

Best regards, L.Meijdam

[Anonymous]

Hello @v-danhe-msft,

Thankyou for linking to the article it helped to better grasp the possibilities to solve this case.

Best regards,

L.Meijdam