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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply

Dynamically change data source from SQL database

Dear everyone,

 

I have several tables in SQL databases, for example: table1, table2, table3, table4. All are at the same server, same database, share the same format, same number and title of columns.

 

I want to import those table from SQL server, customize them with SQL Scripts.

 

Then I want to have a slicer visual which contains the list of all tables

Then I want to create a function to change data source dynamically, import different table to power bi desktop when I select a table name from slicer.

Is that possible?

Can you please help with an example?

Many thanks in advance!
Cindy

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi @Cindy,

 

Some additions to the wonderful post of @ImkeF. The feature changing parameters in the Service is on the way. 

Dynamically_change_data_source_from_SQL_database

 

Another easier workaround is changing the parameters directly without a slicer.

Dynamically_change_data_source_from_SQL_database2

 

 

Best Regards,

Dale

Community Support Team _ Dale
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

5 REPLIES 5
v-jiascu-msft
Employee
Employee

Hi @Cindy,

 

Some additions to the wonderful post of @ImkeF. The feature changing parameters in the Service is on the way. 

Dynamically_change_data_source_from_SQL_database

 

Another easier workaround is changing the parameters directly without a slicer.

Dynamically_change_data_source_from_SQL_database2

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ImkeF
Super User
Super User

It's a bit of a hack on only works in Power BI Desktop, so not in the service: http://www.thebiccountant.com/2017/03/22/use-slicers-for-query-parameters-in-powerbi/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

A different option would be to always load all tables into one big table with an additional column indicating the source.

The users can then do the same "source-selection" on the canvas and the data will be dynamically filtered instantly without having to refresh again.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Dear Imke,

Thanks very much for your solution. The problem is tables on SQL server are generated on monthly basis only. It means that, we only have tables up till present, and no future tables. For example this month is April 2018, then there is no table for May 2018.

It’s fine if it’s only work on power bi desktop. My idea is that we don’t need to go back to advanced editor to edit the query (change month number) every much.

Cheers,
Cindy

Especially with monthly tables I'd personally choose the large consolidated table-version instead.

Are you aware of parameters in Power BI? You can change them from the canvas-view as well without having to hack into your model.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.