Martin on 02 Oct 2018 20:22:09
Currently you can only change parameter values online within imported data sources, this would be useful to have on direct query and live connection data sources also as the ability to move through environments without redeploying is very useful for organisations.
- Comments (12)
RE: Change parameter values within direct query sources
Here we are in March of 2023 and the limitations on PowerBI parameters is still here. Your hands are basically tied with respect to presenting parameter options to users of published reports. In my case, I've got a stored procedure that returns records for a given year. I have a default/current year, but I want users to be able to select from a dropdown too. This sounds exceedingly simple.... and almost all instructions / guides for using this parameter in a dynamic query completely miss the mark & ignore the UI aspect of how to change the value.
RE: Change parameter values within direct query sources
This is definitely needed as currently we can not use the data source dynamic parameters.
RE: Change parameter values within direct query sources
This is a real pain since I cannot distribute the template for data explorer. This is a huge limitation
RE: Change parameter values within direct query sources
That feature is really needed. Otherwise, why the parameters, functions in power query now. All processes being switched to automatic. Please solve this problem.
RE: Change parameter values within direct query sources
Surely obvious that this is needed.
RE: Change parameter values within direct query sources
Please fix this ASAP.
RE: Change parameter values within direct query sources
As a workaround, we can use the Power BI Rest APIs to update the data source. In my case, I had a bunch of tables using the parameterized Azure SQL DB data source.
Source = Sql.Database(AzSqlSrvName, AzSqlDbName)
I had to convert one of the large table to use Direct Query mode. When I published the power bi desktop file to PBI Service and updated the parameters to point to the production SQL db, there were two data source connections established in the Power BI Service. Certainly, the connection string and the MQuery for the Direct Query mode table was cached.
I used the Power BI Rest API and Powershell to update the data source for the Direct Query connection. Here are the steps taken:
1. Get DataSources for a given dataset using https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/getdatasources#code-try-0
The output returned was:
{
"@odata.context": "http://wabi-us-east-a-primary-redirect.analysis.windows.net/v1.0/myorg/$metadata#datasources",
"value": [
{
"datasourceType": "Sql",
"connectionDetails": {
"server": "myazureprod.database.windows.net",
"database": "myPROD_db"
},
"datasourceId": "44abc44a-4ab4-4cd4-444a-4acb4abc4444",
"gatewayId": "11abc11a-1ab1-1cd1-111a-1acb1abc1111"
},
{
"datasourceType": "Sql",
"connectionDetails": {
"server": "myazureqasql.database.windows.net",
"database": "myQA_db"
},
"datasourceId": "00abc000-0000-000a-b000-c000aa00ee0",
"gatewayId": "11abc11a-1ab1-1cd1-111a-1acb1abc1111"
}
]
}
2. Then iterate thru the list of Data Sources returned and determine the "datasourceId" that has a database name “myQA_db”
3. Then call Update Datasources API by passing the “datasourceId” and the new database name to “database” under “connectionDetails” node in the JSON request body.
https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updatedatasources#code-try-0
Here is the JSON request body:
{
"updateDetails": [
RE: Change parameter values within direct query sources
My use case is similar to the one posted on March 12, 2020.
I have a PBI direct query dataset which has SQL Server instance and database names parameterized to allow easy migration between DEV, Test and Prod datasources. The report is deployed to a PBI Service shared capacity workspace. I would like to be able to update the parameters in the service under dataset settings. Currently my only option is to update the parameters in the desktop and republish.
RE: Change parameter values within direct query sources
I vote for the idea.
RE: Change parameter values within direct query sources
Yes, this is needed