Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

dpanaite

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

Submitted by on ‎02-27-2018 09:52 AM

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
See more ideas labeled with:
4 Comments (4 New)
Comments
v-qiuyu-msft
Community Support
Community Support
‎02-28-2018 10:20 PM

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
Impactful Individual
‎02-28-2018 10:22 PM
Status changed to: Needs Info
 
dpanaite
Frequent Visitor
‎03-05-2018 09:31 AM

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
Community Support
‎03-06-2018 02:06 AM

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 

Idea Statuses