Showing results for 
Search instead for 
Did you mean: 

Unlock the power of dynamic parameter binding for Direct Query


Dynamic parameters allow parameters in Power Bi to be bound to a column, in which any time the column is explicitly filtered by the user, the visuals on the page will be updated by reprocessing the queries using the paramter value(s) the user has selected.



As of the writing of this blog, the following limitation apply:

  • Cannot be used with SQL
  • Must be used with Direct Query
  • Cannot be included in any hiearchy (This limitation may or may not apply to custom visuals)
  • All possible values must be enumerated ahead of time.




In this example, we will use Azure Data Explorer. In order to follow this example fully, you will need to have an Active Directory account (you can use a free account).


Before we begin you must enable this feature in the preview settings:




Open Power Bi, and select Get Data, search for Azure Data Explorer (Kusto) and connect to it


Now put in the following details:

Cluster: help

Database: Samples

Query: StormEvents

Data Connectivity Mode: Direct Query


Sign in using any AD account:



Load in the data, and rename the query to StormEvents



Creating the Parameter

In this step, we will leverage ADX's percentile function which is not avialable in Power Query.


Start by creating the Percentile parameter. Set it to required and set the type to Decimal number, with a current value of 50:





Creating the query

Create a new Blank query (right click and choose New source -> Blank Query)

Rename it to ADXQueryFormat

Open the Advanced Editor, and enter in the following query text:

| summarize DamagePropertyPerc = percentile(DamageProperty, #{0}) by State"



If you plan on editing this later, use the Advanced editor to do so.


Here the #{0} is the place holder for our paramter.


Next, Create the query string for the actual ADX query using a Blank query with the following formual:

Text.Format(ADXQueryFormat, {Percentile})


This will be an actual query that gets sent to the server


Next, we just need to substitute the generated query with the "StormEvents" placeholder in our StormEvents query with ADXQuery




Creating the list of allowed values for the parameter

Next, we create a column which will contain all valid values.

Create a new Blank query called Percentiles with the formual:

= #table(type table [Percentile = number], {{50}, {75}, {90}, {96}})




Binding the paramter

Disable loading of the ADXQuery and ADXQueryFormat. This is done by right clicking a data source and unchecking Enable Load


Smash that Close And Load button, to create a direct query connection in your model.

Next in the left side tool bar click the bottom icon for Model.

In the fields view, expand Percentiles and select Percentile

In the properties tab, click Advanced, and in Bind to Paramter, select Percentile.

Also, turn off Is nullable.





Creating the visuals

Now for the east part, create a slicer for the percentiles:



Since we bound the parameter, and we put single select, the slicer will automatically be single select


Now simply add a table visual with the StormEvents fields (Remove the totals row, enabling that will not be covered here today).

Feel free to adjust the values as needed, add more visuals and try it out in your own dataset:






@Anonymous  Did you enable the feature in the Preview Features settings?


@artemusyes I did. And I restarted Power BI.

I also reinstalled Power BI Desktop with the latest version.

@Anonymous Did you change your parameter type to something other than any (true/false or duration is not an option either)?


Would this feature be enabled for SQL queries in the near future? Besides Azure Data Explorer connection, with wich other source it can be implemented?

@Anonymous Currently, SQL Server, Oracle, and Teradata use a legacy system and are not yet supported. All other sources that support Direct Query will work with this. As for a timeline, I don't have any insights into this (I am on the Azure team).


@artemus Then PostgreSQL could be a workarround? or its legacy system is like SQL Server? I could give it a shoot with Azure Database for PostgreSQL.

@Anonymous yes, I think that PostgreSQL is supported.

HI @artemus 

Thanks for this nice article.

Is it possible to connect App insight data in a direct query mode ?

I have tried to use azuredataExplorer connector but it gives me  authorization error while connecting. But If i replace azuredataexplorer.Content with Kusto.Contents in a Advance editor then it works fine.


Currently I am using Kusto.Contents to connect app insight data with a direct query mode and trying to implement dynamic M query parameters for switching the environment. But whenever i am binding the parameters to a modelling tab the visual is giving the error . 


Before binding the parameters it just works fine . Can you please help me in this?



Please Note : I tried to connect app insight in and used the option Share->Query to power BIand paste this query in a blank query of the power BI , but this doesnot work.

If i used this connector or export to power BI option for then it works fine. Only problem is with App insight.






Is it possible to use this with the DataSource Web to make dynamic web calls?

@_Adrian_, No, Direct Query is not supported in using the Web connector:


You will either need to access the data using a supported connector, or build your own.

I followed this example and encountered an issue with the query naming that was causing it to fail. It appears if your query name has spaces in its name, the dynamic parameter binding will fail and no results will come back:



I don't know if this is a bug in the implementation of the binding but posting it for anyone else that might come across this.

What is your favorite Power BI feature release for November 2022?