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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamically Change Data Source

Hi - I have 100 different databases with an identical structure - one for each of my clients.  The data in each are different but the visuals on the report are identical.  For my internal users who work on multiple clients each day, I would like to provide a dropdown of all available Databases and when they choose the database, have the underlying labels and visuals reflect the numbers as they exist in that particular database.  I can easily change the labels but not the source.  I need this to work on both the DeskTop as well as the Published version.  On the Desktop side, I can change the source by creating a paramter - but the only method I could find to alter this parameter was via the Manage/Edit Parameter menu item - but that is not a viable solution and wouldn't work on the Published version.

 

An example of the intended effect is shown below:

 

PowerBi Example.PNG

 

Any help would be deeply appreicated.

 

Thanks.

11 REPLIES 11
Anonymous
Not applicable

Hi - from what I read this only applies to Power BI Desktop.  I am looking for a Power BI Service which will allow a dropdown on the report - not on the toolbar (as that will be locked) down.  I am looking to simply ease of use with the the application without having to interface with any portions of PowerBi Toolbar commands (such as edit parameters).

 

Agin thank you for your suggestion.  However they are pointing me in the direction that PowerBi is not set up to handle having a single front-end that will deal with multi databases each with the same structure - and it may be time to look for another solution.  I am hoping this was not the case but based on my research, talking to a number of PowerBi experts and going on this forum, I don't see a solution that is viable for our situation.

Hi @Anonymous,

Sorry for that, it seems current power bi did not exist features to achieve your requirement.
For this scenario, you can try to find out correspond MS partners that provide similar manages features or try to submit an idea for these requirements to help improve power features.

Power BI Ideas 

Get expert help from Power BI partners 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
parry2k
Super User
Super User

@Anonymous Dynamic Query parameter should able to solve it, check this post

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi - @parry2k, I will certainly give kudos to someone who can answer this question.  I looked at the link and am familiar with setting parameters after the data source has been determined.  I am trying to set the datasource - based on passing a token in PowerBi Service - that identifies the database that that all subsequent queries will use.   I have not found a way to modify the data source in PowerBi Server - even by passing the parameter on the URL string via ?rp:  I did read the SQL Server does not support Direct Query for parameters and I was using SQL Server as my test bed.  However I will be using Snowflake and it would be great if the solution showed a method whereby I could dynamically switch (without my users having to interact with PowerBi) the data source and maintain that workspace independent of other users coming in to the same workspace neededing to access another data source.  I do believe that it may be impossible for PowerBi to handle this as PowerBi is great for Individual companies for but not for companies which have a product that is used by hundreds of clients.  I am hoping to be able to use PowerBi as both a destination and to also use the PowerBi embedded which would be a key portion of our internal product.  And in both cases I want to handle the data source switching and connection refresh behind the scene.

Hi @Anonymous,

Perhaps you can take a look at the power bi premium XMLA endpoint, this function seems to allow you to connect the data sources that host on remote servers.

Dataset connectivity with the XMLA endpoint 
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

Parameterize your connection string with query parameter then you can achieve dynamic change the data source on power bi service side. But this seems not suitable for your scenario, it look more complex than change on single data source.

Using the Power BI Service Parameters to change connection strings (To possibly change between Dev  

Did these records are stored in the same type of databases and they had the same data structure? If this is a case, you can create a file with all conditions(list all the connection and instant names).
Then you can create query tables that reference the configuration file records and they will dynamic change if you update the stored connections in your config file.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi - Ideally I would want to maintain a Direct Connect versus an Import - but that might be OK.  I can get this to work on the Desktop Version by changing the Parameter - however I am not having any success by add a parameter at the end of my URL string ?rp:Database_Name    where Database_Name is the actual name of the Database and not the parameter.  

 

When you say "update the stored connections in your config file."  where is this config file located and how would I change it from the PowerBi Service?

HI @Anonymous,

You are working with a huge amount of data sources. So if you want to fully parameterize the query tables, you need to create a lot of query parameters and they were troubles to be managed.

Use OneDrive for Business links in Power BI Desktop 

Data refresh in Power BI 
For this scenario, I recommend you to save continues into the file. These configurations are stored in the file, you can modify the file contents to update the connection. (you can upload to SharePoint or put it on the local drive and install the gateway to handle and manage this data source)
Then these query tables will be changed based on the new connection strings and you will get the different data source records.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi - thanks again for your reply.  However, in the end, I will have well over 500 individual databases and 10-20 tables per source - and some of these tables will be very large.  I don't think the option of exporting the data to OneDrive would work.  I also have concerns if more than one person is working on same Power Service location at the same time - as these databases must be kept independent of each other as they contain sensitive data specific to each client. I also don't want the clients or our internal business analysts having to modify data sources or having to hit a refresh button as that could be recipe for disaster.  I'm looking for an automatic way of handling this.  It appears that the best solution would be able to alter the M code that sets the source and transformations and somehow trigger that source for a refresh - although the refresh must always be Direct Query as I don't want to import the data for every client or schedule automatic refreshes as I don't know which client data will need to be accessed at any specific time.  Plus I will attaching to Snowflake which should easily handle all of my queries and aggregations.  I'm beginning to think that PowerBi is great for Individual companies for but not for companies which have a product that is used by hundreds of clients.  I am hoping to be able to use PowerBi as both a destination and to also use the PowerBi embedded which would be a key portion of our internal product.

pranit828
Community Champion
Community Champion

Hi @Anonymous ,

I would get data in different tables from all 100 datasources and keep one extra column for all select query as "Datasource" which will return a constant value like 'ClientrDB1' for query 1, 'ClientDB2' for query 2 and so on.

 

Once I have the all the tables created will use the below function on the PBI desktopto create a new table.
UNION(Query1, Query2, Query3,...)

In this newly created table I will use the column "Datasource" in slicer.

 

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
Anonymous
Not applicable

 

Hi, that won't work for us as the tables are quiet large and eventually there will be over 500 different databases each.  Additionally there are multiple large tables and they are updated constantly - so maintaining single versions of each of the large tables would be difficult.  Is there any way that anyone knows of to change the source on the fly.  I know most BI tools have this capability and assume that it must be in PowerBi albeit not easy to find.

 

Thanks for your suggestion though.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.