Custom aggregate function on summarize

Question

Hi at all,
i want calculate a statistic mode on a colummn during summarize a table.
My CalculateMode functions that i try are:

 .create function CalculateMode(Action:int, Asset:string, Start:long, End:long) {
    Event
        | where JsonPath between (Start .. End) and IdAction == Action and IdDevice == Device
        | summarize Count = countif(isnotnull(Result) and isnotempty(Result)) by tostring(Result)
        | top 1 by Count desc
        | project ActionResult
    }

OR

.create function CalculateMode(T:(data:dynamic)) {
T
    | summarize Count = countif(isnotnull(data) and isnotempty(data)) by tostring(data)
    | top 1 by Count desc
    | project data
}

when i using first coding on summarizing:

 Event
        | summarize Result = CalculateMode(toint(IdAction), tostring(IdDevice), Start, End) by Category

Obtain this error No tabular expression statement found and
when i using second coding on summarizing:

Event
    | summarize Result = CalculateMode(Result) by Category

Obtain this error CalculateMode(): argument #1 must be a tabular expression

What can i do?
Where am i wrong?
Thanks

Answer 1

Hello @Marco Apa ,

Please check mv-apply operator (it allows run a sub-query over data in a dynamic column):
https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/mv-applyoperator.

Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.