# Advanced usages of Azure Data Explorer with Power BI Dynamic Parameters

- Link: https://saguiitay.com/advanced-usages-of-azure-data-explorer-with-with-power-bi-dynamic-parameters/
- Published: 2021-03-09T18:46:39+02:00
- Author: Itay

This is the fourth 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: Using Azure Data Explorer with Power BI Dynamic Parameters](https://saguiitay.com/using-pbi-parameters-with-azure-data-explorer/)
 * [Part 4 (this post): Advanced usages of Azure Data Explorer with 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/)

In the previous post, we saw how we can use Power BI’s new dynamic parameters feature
to generate queries based on the values selected in slicers. There are a few scenarios
that are perfect fit for this feature, which I’ll cover in this post.

## Azure Data Explorer features not available in Power BI

There are some features, such as percentiles calculations and regular expressions
handling, which are not available in Power BI. This means that if you’d like to 
use them in your reports, you’ll have to resort to KQL. However, if you don’t “inject”
your filters early on in the query, your cluster will waste a lot of time and resources
to calculate data that you don’t need.

Let’s continue with our StormEvents sample. Let’s say that we’d like to know the
50th, 95th and 99th percentile of total damage done by EventType. If I were to write
such a query in Azure Data Explorer, I’d end up with something along the following:

    ```wp-block-preformatted
    StormEvents
    | extend TotalDamage = DamageCrops + DamageProperty
    | summarize percentiles(TotalDamage, 50, 95, 99) by EventType
    ```

And if I’m interested in the data for a specific State, I’d add the filter as close
to the table name as possible – we want to filter early, in order to reduce computation
effort:

    ```wp-block-preformatted
    StormEvents
    | where State == 'ALABAMA'
    | extend TotalDamage = DamageCrops + DamageProperty
    | summarize percentiles(TotalDamage, 50, 95, 99) by EventType
    ```

Since percentiles is not supported in Power BI, my base query will be similar to
the first KQL query above, and any filtering will be added at the end. However, 
the first query doesn’t return a State column, making it impossible to filter based
on it.

    ```wp-block-preformatted
    // THIS DOES NOT WORK
    StormEvents
    | extend TotalDamage = DamageCrops + DamageProperty
    | summarize percentiles(TotalDamage, 50, 95, 99) by EventType
    | where State == 'ALABAMA'
    ```

This is where a dynamic M parameter is a perfect fit. I can use the same query construction
mechanism we used in the previous post to generate a query using the dynamic parameter,
in order to generate our desired query:

    ```wp-block-code
    let
        StateFilter = if (SelectedStates is null or Text.Length(SelectedStates) = 0) then "" else " | where State == '" & SelectedStates & "' ",
        Query = Text.Combine({
            "StormEvents ",
            StateFilter,
            "| extend TotalDamage = DamageCrops + DamageProperty | summarize (P50, P95, P99)=percentiles(TotalDamage, 50, 95, 99) by EventType"
        }),
        Source = AzureDataExplorer.Contents("https://help.kusto.windows.net", "Samples", Query, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null])
    in
        Source
    ```

This will generate queries such as the following:

    ```wp-block-code
    StormEvents | where State == 'ALASKA' | extend TotalDamage = DamageCrops + DamageProperty | summarize (P50, P95, P99)=percentiles(TotalDamage, 50, 95, 99) by EventType
    // The part below is generated by PowerBI for the visualization
    | summarize ["a0"]=sum(["P99"]), ["a1"]=sum(["P50"]) by ["EventType"]
    | limit 1000001
    ```

And you can see the final results in the animation below:

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

## Invoking Azure Data Explorer Functions with Dynamic M Parameters

Another scenario where Dynamic M Parameters shine is when using Azure Data Explorer
Functions. ADX Functions allow you to write complex queries, and reuse them in a
simple manner. Since functions can accept parameters, we can pass a Dynamic M Parameters
to them, thus greatly simplify our dataset.

Let’s assume I created the following ADX Function in my cluster:

    ```wp-block-code
    .create-or-alter function StormsReportedBySource(source:string) {
        StormEvents
        | where Source == source
    }
    ```

I can now use this ADX Function from Power BI, along with my SelectedSources parameter
like this:

    ```wp-block-code
    let
        Source = AzureDataExplorer.Contents("help", "Samples", null, [MaxRows=null, MaxSize=null, NoTruncate=null, AdditionalSetStatements=null]),
        StormsReportedBySource = Source{[Name="StormsReportedBySource"]}[Data],
        Data = StormsReportedBySource(SelectedSources)
    in
        Data
    ```

This results in a dataset that can be used in a visualization:

![Using Azure Data Explorer Function with Dynamic M Parameter](https://i0.wp.com/
saguiitay.com/wp-content/uploads/ADX-PowerBI-Parameters-Function.gif?resize=734%
2C312&ssl=1 "Advanced usages of Azure Data Explorer with Power BI Dynamic Parameters
2")

As you can see – it makes for a very simple Power BI dataset query, which can hide
a lot of complexity that exists in the Azure Data Explorer query. This allows you
to do the heavy-lifting query in Azure Data Explorer, with all its advanced capabilities,
and make sure you pass the correct filters so the query is as efficient as possible.

### _Related_
