[SOLVED] Azure Analytics : Kusto Query Dashboard group by client_CountryOrRegion and so whenever changing UTC time: past 24 hours it shows deviation in result

Azure Analytics : Kusto Query Dashboard group by client_CountryOrRegion and so whenever changing UTC time: past 24 hours it shows deviation in result

pageViews
| where (url contains "https://***.com") 
| summarize TotalUserCount = dcount(user_Id)
| project TotalUserCount

Now when summarizing by client_CountryOrRegion, there is a deviation in result for different time range selected i.e. for 24 days, 2 days, 3 days, 7 days etc... User count by country does not match the total count. Is it due to UTC timezone?

pageViews
| where (url contains "https://***.com") 
| summarize Users= dcount(user_Id) by client_CountryOrRegion

Any help or suggestion would be like oxygen.

Solution

Quoting the documentation of dcount:

The dcount() aggregation function is primarily useful for estimating the cardinality of huge sets. It trades performance for accuracy, and may return a result that varies between executions. The order of inputs may have an effect on its output.

You can increase the accuracy of the estimation by providing the accuracy argument to dcount, e.g. dcount(user_Id, 4). Note that this will improve the estimation (at the cost of query performance), but still won't be 100% accurate. You can read more about this in the doc.