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

Power BI cannot find data source in current file when using dynamic values

Hi,

 

In order to speed up deployments to different environments, I set up each of my queries to take dynamic values for the database source (server/name). These dynamic values are grabbed from a table with hardcoded server/database parameters from each environment. Getting data using this approach works fine; however, Power BI is having difficulty trying to any data sources in current file (click the Data source settings option in the toolbar and navigate to Data sources in current file).

Status: Needs Info
Comments
v-qiuyu-msft
Community Support

Hi @dpanaite,

 

What do you mean about "Power BI is having difficulty trying to any data sources in current file (click the Data source settings option in the toolbar and navigate to Data sources in current file)."? 

 

As you use Query Parameter to dynamically specify server name and database, each time you change the Query Parameter values and apply the change, the data source connection string display in Data source settings is matching the query parameter selected values. 

 

Best Regards,
Qiuyun Yu 

Vicky_Song
Impactful Individual
Status changed to: Needs Info
 
dpanaite
Frequent Visitor

Hi @v-qiuyu-msft,

 

Apologies for the lack of information! Query parameters will work, however, it's a bit tedious to switch between environments when you have multiple parameters that need to be changed. The approach that I've been trying to do is to create a table of parameters, that holds the parameters for each environment:

 

2018-03-05_0910.png

 

Each parameter refrences this table (this example just grabs the first row for simplicity):

2018-03-05_0910_001.png

 

And finally, each query can then use these dynamic values:

 

2018-03-05_0911.png

 

This approach is able to properly use the dynamic Server/Database values and grab the list of Employees. However, Power BI cannot find the connected data source by navigating to the Data source settings section. This causes an issue when deploying the report to Power BI Online, as it cannot find the data source within the report, and cannot link to an on-premise gateway.

 

2018-03-05_0912.png

v-qiuyu-msft
Community Support

Hi @dpanaite,

 

Normally, to use query parameter to change data source dynamically, we can follow Use Case 1 in this blog: http://biinsight.com/power-bi-desktop-query-parameters-part-1/

 

But Query Parameter feature is only available in Power BI desktop currently, it's not supported in Power BI service, you can vote on this same idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13820190-power-bi-online-query-pa...

 

Best Regards,
Qiuyun Yu