Forum Discussion

BusinessAnalyst's avatar
BusinessAnalyst
Icon for Helper I rankHelper I
8 years ago
Solved

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

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

5 Replies

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      8 years ago

      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.

      • BusinessAnalyst's avatar
        BusinessAnalyst
        Icon for Helper I rankHelper I
        8 years ago
        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
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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