Charts on distinct values

EWG IT Corporate

I have a dataset, which is the result of a join so the ID is not a unique key:

+————+——————+——————-+—————|
| — ID — | — Name — | — Gender — | — Role — |
+————+——————+——————-+—————|
| 001 | Alex B | Male | User |
| 001 | Alex B | Male | Admin |
| 002 | John C | Male | Agent |
| 003 | Drew H | Female | User |
| 003 | Drew H | Female | Agent |
+————+——————+——————-+—————|

I know how to use count distinct with CASE to get the right numbers on Single Value charts, but I would like to find a way to build other charts counting ID just once.

For instance, if I build a Pie Chart on Gender (and Count of Gender), I will get this:
Male: 3
Female: 2

But I would like to get this instead:
Male: 2
Female: 1

How can I get this?

Thanks,
Gianfranco

david_cunningham

You can do COUNT(DISTINCT id) @EWG IT Corporate

Your example data

Beast Mode

Result

david_cunningham

COUNT(DISTINCT Gender)

You don't have to have it in a case statement.

If this answers your question, please 'like' and 'accept' it as the answer 😁

EWG IT Corporate

Hi David,

thanks for your feedback. The issue is not counting distinct genders but counting distinct IDs.

If I apply your solution, I get this:

Male: 1
Female: 1

david_cunningham

You can do COUNT(DISTINCT id) @EWG IT Corporate

Your example data

Beast Mode

Result

EWG IT Corporate

This one works!

Thanks a lot.