Extended Distinct count

Question & Answer
,
1

Hi, I have a table where I have lots of records.

Here is an example:

image

I want an output where I have the number of records by category.

If I count in Quicksight, then I get as result:
BLUE 4
RED 3
NOTHING 3

But now I want that each number is counted only once.
When I do a Distinct Count I get the following:

BLUE 3
RED 2
NOTHING 3

But I want to get a different result.
RED counts first, then Blue and finally Nothing.
So if number one contains an entry with RED, it will be counted at RED. The value at BLUE is not counted anymore.
So once a value is counted, it is not counted again.

Any idea how I can implement something like this?

2

Hmm,

Can you try something like this?

ifelse(distinctCountOver(ifelse({Category}='RED',{nummer},NULL),
[],PRE_AGG)>0,distinctCountOver(ifelse({Category}='RED',{nummer},NULL)
,[],PRE_AGG),etc...)

On the etc you would check your other colors.

Let me know if that works