Advanced usages of Azure Data Explorer with Power BI Dynamic Parameters

This is the fourth post in this series, discussing the usage of Power BI to visualize Azure Data Explorer data. 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:

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:

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.

// 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:

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:

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:

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:

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

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