Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
v-danhe-msft
Microsoft Employee
Microsoft Employee

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.

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

Birdjo
Resolver II
Resolver II

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:
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
Birdjo
Resolver II
Resolver II

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:
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!

v-danhe-msft
Microsoft Employee
Microsoft Employee

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.

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.
Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors