cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Continued Contributor
Continued Contributor

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 🙂

 

Best Regards,

L.Meijdam

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft
Microsoft

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.

View solution in original post

Highlighted
Resolver II
Resolver II

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!

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
Microsoft

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.

View solution in original post

Highlighted
Resolver II
Resolver II

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!

View solution in original post

Highlighted
Continued Contributor
Continued Contributor

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

Highlighted
Continued Contributor
Continued Contributor

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 🙂

Best regards, L.Meijdam

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors