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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous

Hello, I am trying to implement a dynamic parameter binding, but I do not see the dropdown "Bind to Parameter".

2020-10-28 12_21_45-Untitled - Power BI Desktop.png

I have the latest Power BI desktop (2.86.902 Oct 2020) and the preview setting is enabled.

What am I missing?

thx

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

Anonymous

@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)?

@Anonymous , I should also mention that SQL is not supported (various flavors, along with a few others like Relational SAP Hana)

Anonymous

@artemusThe parameter is of type Text. But I don't even see the dropdown to bind to any parameter.

The datasource is a Kusto db.

 

Anonymous

@artemusfound it! the query was not using Direct Query. Now I see the dropdown. Thank you for your help.

I do not see the dropdown "Bind to Parameter" also.
I try only binde parameter with table without another date. 
I hav the last relise and settings are2.JPG1.JPG3.JPG right. 

@IrynaKulaginaTF You don't have any eligible direct query loaded into your model, this option will not appear.

 

Also double check that you have the preview feature enabled.

 

Anonymous

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

Anonymous

@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 . 

PBILover_0-1613589927925.png

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

 

 

Please Note : I tried to connect app insight in https://dataexplorer.azure.com/clusters/ 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 https://help.kusto.windows.net/ then it works fine. Only problem is with App insight.

Thanks

Thanks.

 

 

 

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