# How to find a unique count exact value
**URL:** https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018
**Category:** Kibana
**Created:** [October 24, 2017, 8:54am UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018 "2017-10-24T08:54:46Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@piotr.dela](https://discuss.elastic.co/u/piotr.dela)
#### Post date: [October 24, 2017, 8:54am UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018/1 "2017-10-24T08:54:46Z")
Hello,
I'm working on Kibana visualizations at my current project at work and we have found an issue with this unique count metric.
We already know that this metric is only an approximation and it doesn't get an exact value. But we still need to have an exact one.
Could you recommend any workaround to this problem? Now I will give more details about the index and the data are stored there.
Let's say we have an index 'registrations' where we keep persons (somehow connected with football) registrations. Every single person can be registered in many organizations in a different sport.
For example:
Robert Lewandowski (RL) plays Football in Bayern Monachium (BM) as a Professional player
Robert Lewandowski (RL) also plays Futsal in Bayern Monachium (BM) as an Amateur
Christiano Ronaldo (CR7) plays Football in Real Madrid (RM) as a Professional player
Lionel Messi plays Football (LM) in FC Barcelona (FCB) as a Professional player
In 'registrations' index we keep ids of Clubs and Persons so it looks moreless like that:
personId | organizationId | discipline | level
RL | BM | Football | Professional
RL | BM | Futsal | Amateur
CR7 | RM | Football | Professional
LM | FCB | Football | Professional
Then we would like to know for example:
1. How many players are registered in total - in this case should be 3
2. How many players are registered in specified organizations (grouping)
BM -\> 1
RM -\> 1
FCB -\> 1
In Kibana we have single dasboard with a few visualisations which are based on same index so that we can use filtering applied to all of them.
Do you have any idea how to workaround this?
Looking forward for help,
Piotr
---
### Author:  [@tsullivan](https://discuss.elastic.co/u/tsullivan)
#### Post date: [October 24, 2017, 8:27pm UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018/2 "2017-10-24T20:27:53Z")
> [@piotr.dela](#):
>
> How many players are registered in total - in this case should be 3
You can use a Metric visualization and just use the "count" metric for this.
> [@piotr.dela](#):
>
> How many players are registered in specified organizations (grouping)
There are many ways to do this, generally in most visualizations, you can:
- use "Unique Count" on the `personId` field as the metric
- use a terms aggregation on the `organizationId` field for the X-Axis (or split rows in a table visualization).
---
### Author:  [@piotr.dela](https://discuss.elastic.co/u/piotr.dela)
#### Post date: [October 25, 2017, 7:36am UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018/3 "2017-10-25T07:36:02Z")
Thanks for a reply.
Actually count metric will return 4 but i want to be 3. In total we have 4 **registrations** but as soon RL is has two registrations we have 3 **unique players** registered in total.
Yes, having terms aggregation on organizationId will do the job but Unique Count metric will return an approximation, but we need an exact value.
---
### Author:  [@tsullivan](https://discuss.elastic.co/u/tsullivan)
#### Post date: [October 25, 2017, 5:48pm UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018/4 "2017-10-25T17:48:09Z")
> [@piotr.dela](#):
>
> Unique Count metric will return an approximation, but we need an exact value.
The Unique Count metric aggregation is a Cardinality aggregation in Elasticsearch, which has an option to control the threshold: [Cardinality aggregation | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-cardinality-aggregation.html#_precision_control)
You can set the threshold value in Kibana using the JSON input:
---
### Author:  [@piotr.dela](https://discuss.elastic.co/u/piotr.dela)
#### Post date: [October 26, 2017, 7:31am UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018/5 "2017-10-26T07:31:16Z")
Yes, that's exactly the one we are using now - Unique Count metric. Well we could potentially use this threshold but this still gives an approximation - not an exact value. In other words, how we can get the **exact** unique count value?
---
### Author:  [@tsullivan](https://discuss.elastic.co/u/tsullivan)
#### Post date: [October 26, 2017, 4:41pm UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018/6 "2017-10-26T16:41:55Z")
In Elasticsearch there isn't a separate kind of metric aggregation for exact unique count, there's only cardinality. There's also the terms aggregation, which is a bucket aggregation and you can get the count of each term.
By controlling the threshold value of the cardinality aggregation, you are spending more memory for the cost of accuracy, so you can make that as high as you want (refer to the documentation for more details - there is a limit of 40,000), but just watch out for memory usage issues.
---
### Author:  [@PeKaDe](https://discuss.elastic.co/u/PeKaDe)
#### Post date: [October 29, 2017, 11:21am UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018/7 "2017-10-29T11:21:48Z")
Hi Tim,
I'm working on the same project as Piotr does. I read your answer and I am already prepared to say to our client that the filtering on their dashboard will not work. However, still hoping that we are having some kind of misunderstanding here, I would like to have a 100% confirmation that Elastic does not provide any solution to the issue presented.
The reason I'm pushing this is that I feel that we have a simple requirement (filtering the whole dashboard based on OrganizationID) for a super simple data model. There are 2 one-to-many relationships and in the SQL world it would take me no time to have a dashboard based on FK's and distinct count up and running. I would find it really surprising if we were the first team to contact you with such a need. This makes me hope, as mentioned in the beginning, that a solution/workaround exists, but we were perhaps just not clear enough in explaining the problem.
Many thanks for coming back to us again on this!
PKD
(Piotr Kuc-Dzierżawski)
---
### Author:  [@tsullivan](https://discuss.elastic.co/u/tsullivan)
#### Post date: [October 30, 2017, 4:59pm UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018/8 "2017-10-30T16:59:33Z")
> [@PeKaDe](#):
>
> There are 2 one-to-many relationships and in the SQL world it would take me no time to have a dashboard based on FK's and distinct count up and running
Solving this problem in an SQL environment will be very different than the best way to solve it in Elasticsearch, because Elasticsearch is geared for document storage where a document has all the fields of information that are useful for analysis, versus SQL where entities are arranged in their tables and reference each other with foreign keys.
Please take a look at an article in our documentation titled "Modeling Your Data": [Modeling Your Data | Elasticsearch: The Definitive Guide [2.x] | Elastic](https://www.elastic.co/guide/en/elasticsearch/guide/current/modeling-your-data.html) - hopefully it will give you some ideas of how the data can be arranged so that a simple filter can give you the quick and precise answer that you need.
Also, since we're getting more into capabilities of Elasticsearch and how to model the data, feel free to open a thread under the Elasticsearch topic and if there is a misunderstanding on my part, I apologize if that's the case, but you may get a better answer than here in the Kibana topic.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 27, 2017, 5:00pm UTC](https://discuss.elastic.co/t/how-to-find-a-unique-count-exact-value/105018/9 "2017-11-27T17:00:02Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.