Calculated field count groupby based on attribute values

Question & Answer
,
1

Hello,
I’m trying to build a calculated value to count of some aggregation based on some field values.
I have a dataset with the properties “title” and “status” and I want to get the distinct count grouped by title where status=“PASSED” and there is no status=“FAILED”

distinct_countIf( title , status=“PASSED”) does not give me the correct answer as I don’t want to count the titles that also have status=“FAILED”.

The athena query that gives me to correct number of rows in the dataset is:
select
title,
COUNT(CASE WHEN status=‘PASSED’ THEN 1 END) as PASSED,
COUNT(CASE WHEN status=‘FAILED’ THEN 1 END) as FAILED
from database.table
group by title
having COUNT(CASE WHEN status=‘PASSED’ THEN 1 END) > 0 and COUNT(CASE WHEN status=‘FAILED’ THEN 1 END) = 0

Is there any way to achieve that through a calculated field?

2

Hi asirvent! Can a title have more than one status? Like are there more than one row per title and some rows may say PASSED and some may say FAILED?

If so, maybe one technique you can try a calc like this:
maxOver(ifelse(status=‘PASSED’, 1, 0), [title], PRE_AGG) + maxOver(ifelse(status=‘FAILED’, 2, 0), [title], PRE_AGG)

Then add it as a filter to your visual and set it equal to 1.

3

Hi, @asirvent. Did @Jesse’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!