I have the below table in quicksight and I want to create a KPI where I will count only the cas_ids where ALL file_ids are No.
Sor for example in the below the KPI will have 1 as only 51866 case has all file_ids as No where 64055 has some Yes.
| case_id | file_id | support |
|---|---|---|
| 51866 | 5681 | No |
| 51866 | 5682 | No |
| 51866 | 5683 | No |
| 51866 | 5684 | No |
| 64055 | 6102 | No |
| 64055 | 6103 | Yes |
| 64055 | 6104 | No |
| 64055 | 6105 | Yes |
Hello @pantelis , Hope this message finds you well!!!
To solve this, my suggestion is to create a calculated field that identify if all file_ids for a case_id are “No”. Maybe something like this work: ifelse(sum(ifelse({support} = 'no', 1, 0)) = count({file_id}), 1, 0).
Then, create a KPI visual and drag case_id to the value field, and apply a filter to include only case_ids where the calculated field equals 1. This will count only the case_ids where all file_ids are “No”.
tell me if it’s helps you 
Thanks @lary_andr for the reply. With this calculated field as with some others I used the case_ids appear correctly when the visual is a table but when I try to create a KPI and have the distinct count of the case_ids I get No data.
To resolve the issue with the KPI showing “No data”, ensure that the KPI is set to use “Distinct Count” for case_id and verify that the filters are correctly applied to include only the case_ids where the calculated field equals 1. If it’s not working yet, try recreating the KPI from scratch, ensuring all settings are correctly configured.
Hi @pantelis,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post or were you able to find a work around?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Thank you!
Hi @pantelis,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
Thank you!
