cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LMeijdam Established Member
Established Member

Dynamic database use

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 Smiley Happy

 

Best Regards,

L.Meijdam

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic database use

Hi @LMeijdam,

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.

1.PNG

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

2.PNG

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

3.PNG

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Birdjo Member
Member

Re: Dynamic database use

Hello @LMeijdam,

 

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:
image.png

 

2. Repat for table 2

 

3. Appending queries 

image.png

 

4. Visualize as you wish:
image.png

 

I hope that solves your issue!

4 REPLIES 4
Community Support Team
Community Support Team

Re: Dynamic database use

Hi @LMeijdam,

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.

1.PNG

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

2.PNG

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

3.PNG

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

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Birdjo Member
Member

Re: Dynamic database use

Hello @LMeijdam,

 

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:
image.png

 

2. Repat for table 2

 

3. Appending queries 

image.png

 

4. Visualize as you wish:
image.png

 

I hope that solves your issue!

LMeijdam Established Member
Established Member

Re: Dynamic database use

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

LMeijdam Established Member
Established Member

Re: Dynamic database use

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 Smiley Happy

Best regards, L.Meijdam