Parameters in Direct Query

[Pravina2514]

Hello Team,

I want to change my local server to on premise server using dataset parameters. I have loaded data using direct query. 

I could change my server with using parameter in import mode. I am trying to achieve same result with direct query but Power bi Service is not able to identify parameters when I use direct query.  Please let me know how I can achieve this.

[GilbertQ · Accepted solution]

Hi there

As far as I know there is nothing else at the moment unfortunately.

[Anonymous]

Bingoooo..

Got solution finaly...if you download latest version of power bi dekstop and publish the report.your parametrs are enable in services and you can make it dyanamic.

latest version link-https://www.microsoft.com/en-us/download/details.aspx?id=58494

version -2.82.5858.1161

[Anonymous]

@Anonymous  I have tired this by downloading latest version 2.82.5858.1161 and published the report with direct query contains paramter but parameters are not showing up in power bi online(app.powerbi.com.) Could you please elobarate the steps furher if possible. Any references could be highly appreciated.

Just for your information, I am holding Trail Pro License. Is that really matters? 
Thanks

Mohan Kumar

[Anonymous]

Hi @Anonymous  i am not sure about trail pro License. Curruntly i am using pro  license.

Step 1-Create a report in PBI desktop using latest version 

step 2-In Get data-serverdetail-direct query-advance option -write plane sql  query-done

step 3-Go to transform data-transform data-advance editor-change query with your paramter (dont use any filters)close and apply.

step 4- publish report.

Note-Desktop and services must use same user account.

please follow the below screenshot-

[GilbertQ]

Hi there

As far as I know parameters cannot be used with DirectQuery?

[Anonymous]

@GilbertQ  please see the below screenshot where i created report using direct query and for same report i am able to pass parameter in services.

[Anonymous]

Hi @Anonymous 
Is the SQL Server On-Prem or  Azure?
Are you using any gateways?

[Anonymous]

@Anonymous Sql server not using any gateway.

Direct query with parameters only support sql server as data source.

if i answer your question mark as solved.

Thanks.

[Anonymous]

Hi @GilbertQ ,

Can we change Paramter values in direct query mode using PowerShell?

[Anonymous · Accepted solution]

Yes by using rest api you can update parametrs in direct query.

Step 1- Take direct query mode and in advance option write a normal query.

Step 2- Go to adavance editor and change that query with parameter.

like

let
Source = Sql.Database("host name", "database_name",
[Query="select * from [schema name].["& paramter 1 & "_retailername_"& paramter2 &"] (NOLOCK)"])
in
Source

In the above example i chnage my direct query using parameter.

If you want to chnage thease parameters using powershell plz follow the following link-

https://github.com/mycodehub2205/Powershell-For-PowerBI/commit/eb1263699640beb6ec33a822c45a0c56190a7...

If i answer your question marked as solved..

[lbendlin]

You can try to abuse the Deployment Pipeline parameterization for that.  Direct Query is not listed in the limitations.

https://docs.microsoft.com/en-us/power-bi/create-reports/deployment-pipelines-process#unsupported-it...

[Pravina2514]

but, it requires premium capacity and my organization has pro license.

[GilbertQ]

Hi there

Currently it is not possible to use parameters with DirectQuery

You can vote for the idea at https://ideas.powerbi.com

[Pravina2514]

is there any other workaround?

[GilbertQ · Accepted solution]

Hi there

As far as I know there is nothing else at the moment unfortunately.

[Anonymous]

@GilbertQ @Pravina2514 @Anonymous @Anonymous 

After lots of research finaly found how to enable parameter in direct query.

follow the following setting in you power bi desktop latest version(June 2020)

File-Option and setting-Preview features-enable-store datasets using enhance metadata.

If it work mark as solution.

Thanks.