Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

artemus

Unlock the power of dynamic parameter binding for Direct Query

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:

 

artemus_4-1603322750732.png

 

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

artemus_2-1603319161214.png

Now put in the following details:

Cluster: help

Database: Samples

Query: StormEvents

Data Connectivity Mode: Direct Query

artemus_3-1603319477039.png

Sign in using any AD account:

artemus_4-1603319653550.png

 

Load in the data, and rename the query to StormEvents

artemus_5-1603319962355.png

 

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:

 

artemus_6-1603320398732.png

 

 

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"

artemus_10-1603321579931.png

 

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})

artemus_9-1603321158128.png

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

 
 

artemus_0-1603321900420.png

 

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}})

artemus_2-1603322250461.png

 

 

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.

 

artemus_3-1603322551092.png

 

 

Creating the visuals

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

artemus_5-1603322850725.png

 

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_6-1603323102649.png

 

 

 

Comments