Unlock the power of dynamic parameter binding for Direct Query

[artemus]

Introduction

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.

Limitations

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.

Walkthough

Setup

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 outlook.com 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:

"StormEvents
| 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:

[artemus]

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

https://docs.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources

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

[iamgoce]

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.

[Anonymous]

I am using sql server as a source. i couldnt find Bind to Pramater option. It is not available.  I am using 2.108.825.0 64 bit (August 2022) version.

Is the Bind to paramter feature available  when we use sql server direct query as a source? do i need to use ay other version of PBI desktop?

[StephenL_CA]

I have PBI Desktop Version: 2.118.828.0 , I don't even have this Dynamic M Query parameters feature under Preview Features.  My data source is SQL Server, now in 2023, is SQL Server supported?

Please help.

[Anonymous]

Hi,

I did the same thing with my data source but when I published to the workspace but the table visual I am showing shows this error "can't display the visual".