Azure Application Insights Analytics Query for exporting to Power BI

Question

I need some help for writing queries in AI Analytics with which i can generate kusto query and export the result to Power BI . I am new to query language in general ( didn't have much exp in sql also )

So , I have created a couple of custom events( lets say statusA and statusB ) in my app , which i will be getting in Customdimensions . statusA and StatusB will be having lets say true or false . I should be able to get the result data as Status containing count of true/false values of StatusA and statusB each , so that i will be able to render it in a piechart.

the query which i tried :

customEvents | extend queryA = customDimensions.['StatusA'] , queryB = customDimensions.['StatusB'] | where queryA = true or queryB = true | project queryA, queryB | summarize count() by queryA , queryB

Result I got is this

I want the result to be like this in pie chart : Pie chart

Answer 1

Try this query:

customEvents 
| extend queryA = tostring(customDimensions.['StatusA']) , queryB = tostring(customDimensions.['StatusB'])
| extend status = iff(queryA == 'true', "StatusA", (iff(queryB == 'true', "StatusB", "N/A")))
| summarize count() by status
| render piechart

To save this overhead of comparing values, you can simply report in the customDimensions on queryType and assign it with the value of QueryA or QueryB. Then it would be only a matter of using summarize count() by queryType.

Answer 2

A simple solution could be converting those true/falses into ints using iff, and then summing.

Try:

customEvents 
| extend queryA = tostring(customDimensions.['StatusA']) , queryB = tostring(customDimensions.['StatusB'])
| extend queryACount = iff(queryA == 'true', 1, 0)
| extend queryBCount = iff(queryB == 'true', 1, 0)
| summarize sum(queryACount),  sum(queryBCount)
| render piechart