Creating Flags based on conditions for easy counting in visuals

Hi there - I am relatively new to QuickSight and am running into an issue creating a “flag” at the data level using conditions. We have one flat table/entity that has “Id,” and has Foster Child data joined to it (via “Child ID” or ‘c_id’ in the screenshot below).

I have a feeling it is using a countOver function or something similar, but can’t quite get it to work as expected. Appreciate any help you can provide!


  • The other id’s on here are ‘m_id’ and ‘f_id’, which are Foster Mother and Father IDs for the given person/record.

  • These Mother and Father IDs are all included in the entity somewhere in the ‘id’ column

  • I’m trying to create a “Is Foster Parent Flag” so that on the record for the Foster Mother and the Father, I show a 1 if their ID is somewhere in the ‘m_id’ or ‘f_id’ column, but a 0 if they’re not indicated anywhere in those columns.

  • Here’s an example:

    • As you can see for IDs 317 and 4892, their parents are ID 325 (Mother) and ID 37294 (Father).
    • I am trying to create a Flag that would show:
      • 0 for IDs 317 and 4892, as they are Foster Children and not Foster Parents.
      • 1 for IDs 325, 24770 and 37294 as they are listed somewhere in the ‘m_id’ or ‘f_id’ columns

Is this a SPICE dataset or a dataset using direct query?

If using direct query, I believe the easiest approach would be to use SQL like

select id, c_id, m_id, f_id,
case when id in (select m_id from c_id_table) or id in (select f_id from c_id_table) then 1 else 0 end as flag
from table