# Using Azure Data Explorer with Power BI Dynamic Parameters

- Link: https://saguiitay.com/using-pbi-parameters-with-azure-data-explorer/
- Published: 2021-02-25T13:17:30+02:00
- Author: Itay

This is the third post in this series, discussing the usage of Power BI to visualize
Azure Data Explorer data. You can read the rest of the posts here:

 * [[[Part 1: Querying Azure Data Explorer using Power BI](https://saguiitay.com/querying-azure-data-explorer-using-power-bi/)
 * [[[Part 2: Using dimensions when querying Azure Data Explorer using Power BI](https://saguiitay.com/using-dimensions-when-querying-azure-data-explorer-using-power-bi/)
 * [[[[[Part 3 (this post): Using Azure Data Explorer with Power BI Dynamic Parameters](https://saguiitay.com/using-pbi-parameters-with-azure-data-explorer/)
 * [Part 4: Advanced usages of Azure Data Explorer with Power BI Dynamic Parameters](https://saguiitay.com/advanced-usages-of-azure-data-explorer-with-with-power-bi-dynamic-parameters/)
 * [Part 5: Advanced options of the Azure Data Explorer Connector for Power BI](https://saguiitay.com/advanced-options-of-the-azure-data-explorer-connector-for-power-bi/)

If you followed those posts, we now have a report that uses 2 dimension tables for
States and Sources loaded in Import mode (to reduce the number of queries being 
sent to ADX), and a facts table with StormEvents data, using Direct query mode (
to have fresh, up-to-date data). The next thing to investigate, is how to use the
dimension table, along with the recently announced Dynamic Parameters feature of
Power BI.

## Configuring the Dynamic Parameters in Power BI

First, let’s define SelectedStates and SelectedSources parameters in our model. 
We’ll define 2 parameters of type Text, mark them as required, and provide a default
value:

![Create Power BI Dynamic Parameters for Azure Data Explorer](https://i0.wp.com/
saguiitay.com/wp-content/uploads/image-11.png?resize=601%2C651&ssl=1 "Using Azure
Data Explorer with Power BI Dynamic Parameters 1")

Next, we need to bind each one of these parameters to the respective column in the
dimension tables. This is done by selecting the columns in the model, and under 
the Advanced section, select the parameter in the “Bind to parameter” option:

![Bind Power BI Dynamic Parameters to Azure Data Explorer column](https://i0.wp.
com/saguiitay.com/wp-content/uploads/image-12.png?resize=516%2C558&ssl=1 "Using 
Azure Data Explorer with Power BI Dynamic Parameters 2")

What this binding means, is that whenever we select a value in a slicer bound to
the column, the Parameter’s value will be updated to the selected value. This will
allow us to perform filtering, pass values to ADX functions, and so on. For this
post, I’ll set Multi-select to false for the States, and to true for the Sources.
This will allow me to demonstrate how to handle both a single value, and multiple
values.

## Updating the Azure Data Explorer queries

Now that we expect the selected values to be bound to our Parameters, we need to
use those parameters in the Azure Data Explorer data source. We can do this in multiple
ways:

 1. We can manually construct the ADX query we pass to the connector – I’ll use this
    method for the simpler State Parameter.
 2. We can use M to fold our query, saving us from having to manipulate strings – I’ll
    use this method for the multi-values Sources Parameter
 3. In a future post, when I’ll discuss using Azure Data Explorer functions in Power
    BI, we’ll also be able to pass the Parameter to an ADX function.

**NOTICE: Options #2 and #3 are much more secure, since they avoid having to handle“
KQL Injection” issues – if you work with strings, it’s your responsibility to make
sure that the values passed to the Parameters are encoded as needed. One option 
to do so, is to use KQL’s [query parameters statements](https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/queryparametersstatement),
and passing the parameters as a default value.**

For the State Parameter, I’ll first check is it’s not empty, and construct a KQL
filter statement, which I’ll concat to my query:

    ```notranslate
    StateFilter = if (SelectedStates is null or Text.Length(SelectedStates) = 0) then "" else " | where State == '" &amp; SelectedStates &amp; "'",
    Source = AzureDataExplorer.Contents("https://help.kusto.windows.net", "Samples", "StormEvents" &amp; StateFilter, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null])
    ```

![Using Azure Data Explorer with Power BI Dynamic Parameters Query](https://i0.wp.
com/saguiitay.com/wp-content/uploads/image-13.png?resize=825%2C230&ssl=1 "Using 
Azure Data Explorer with Power BI Dynamic Parameters 3")

For the multi-values Sources Parameter, we’ll just make sure that the value is of
type List. If it is, we’ll use it to invoke M’s List.Contains() function. If not,
we’ll assume it’s a single text value, and create list of it first.

    ```notranslate
    SourcesList = if (SelectedSources is null) then { } else if (SelectedSources is text) then { SelectedSources } else SelectedSources,
    Folded = if (List.Count(SourcesList) > 0) then 
            Table.SelectRows(Source, each List.Contains(SourcesList, [Source]))
        else
            Source
    ```

Our final M query should look something like this:

    ```notranslate
    let
        StateFilter = if (SelectedStates is null or Text.Length(SelectedStates) = 0) then "" else " | where State == '" &amp; SelectedStates &amp; "'",
        Source = AzureDataExplorer.Contents("https://help.kusto.windows.net", "Samples", "StormEvents" &amp; StateFilter, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null]),
        SourcesList = if (SelectedSources is null) then { } else if (SelectedSources is text) then { SelectedSources } else SelectedSources,
        Folded = if (List.Count(SourcesList) > 0) then 
                Table.SelectRows(Source, each List.Contains(SourcesList, [Source]))
            else
                Source
    in
        Folded
    ```

We can check the KQL query that will be sent to our Azure Data Explorer cluster 
by selecting the “View Native Query” feature:

![View Native Using Azure Data Explorer Query with Power BI Dynamic Parameters](
https://i0.wp.com/saguiitay.com/wp-content/uploads/image-14.png?resize=311%2C473&
ssl=1 "Using Azure Data Explorer with Power BI Dynamic Parameters 4")

And you should see a KQL query similar to this, just as we expected

    ```notranslate
    StormEvents | where State == 'ALABAMA'
    | where ["Source"] in ("Newspaper")
    ```

## Updating the relationships in our Power BI Model

Since we’re now responsible for “injecting” the selected values of slicers into 
our query, we need to remove any relationships we have between our dimensions tables
and our facts tables. Otherwise, we’ll end up with conflicting and inconsistent 
filtering, which will result in empty result sets.

## Visualizing our data

At this point, there’s nothing left to do – we can start using our visualizations
just like we did before, and see the data refreshed as we make selections in the
slicers:

![ADX PowerBI Parameters](https://i0.wp.com/saguiitay.com/wp-content/uploads/ADX-
PowerBI-Parameters.gif?resize=825%2C267&ssl=1 "Using Azure Data Explorer with Power
BI Dynamic Parameters 5")

### _Related_
