How to do distinct count and if else statement in quicksight?

Question

New in quicksight. Here is my problem:

Score : > 98% = Pass, < 98% = Fail

ID: I have duplicates of IDs on my data

score	id
95%	1
95%	1
98%	2
97%	3
97%	3

I only want to count distinct ids with passing score? How do I write a formula for that?

Thanks.

Answer 1

Of you just want to count the distinct id with a socre 98% or more, you can use a filter for the score and a visual setting the in the value field the id(count Distinct).

If you want to see the Pass/Fails you can create a new Calculated Field with the score logic in an ifelse, something like this:

ifelse({score}>=98%, "Pass", "Fails")

And the use that Calculated field in an visual using the count Distinct of the id as value.