How can I select distinct values in a column rather than distinc column ?

[Gamleur84]

Hi

I am trying to get the count of unique RoleInstance per 10 min

MetricsLogs
| distinct RoleInstance
| summarize TimeGenerated, AggregatedValue = count(RoleInstance) by bin(TimeGenerated, 10m)

It does not work with the following error 'summarize' operator: Failed to resolve scalar expression named 'TimeGenerated'

Then, I tried this, but it returns distinct rows.

MetricsLogs
| distinct RoleInstance, TimeGenerated
| summarize TimeGenerated, AggregatedValue = count(RoleInstance) by bin(TimeGenerated, 10m)

Sorry, I am sure it is easy to fix :(

[#2 · Accepted solution]

@Gamleur84 

How about, either of these?

Perf
| where CounterName =="% Used Space"
| summarize AggregatedValue = count(CounterName) by bin(TimeGenerated, 10m), CounterName

// shows unique ConunterValues per CounterName 
// source: https://docs.microsoft.com/en-us/azure/kusto/query/dcount-aggfunction
Perf
| where CounterName =="% Used Space"
| summarize AggregatedValue = dcount(CounterValue) by bin(TimeGenerated, 10m), CounterName

[#3]

@CliveWatson Thanks Clive. I am not sure what is the most impressive. The fact that you understood my issue although my explanation was messy, incomplete and not well formatted or the accuracy of your answer.

The answer you provided works well for my case, I am keen to understand the explanation. Shall I use distinct when looking for distinct rows rather than values ?

I suspect the key is dcount(), i read about it but I was reluctant to use it because of this:  returns an estimate for the number. The dcount() aggregation function is primarily useful for estimating the cardinality of huge sets. It trades performance for accuracy

In my case, the accuracy is important, but I am analyzing a small set...