Get multiple results when checkbox is checked (true)

Showcase Tips and Hacks
1

I found out that when you want to return results from a filter and one of the conditions involves a column with a “Checkbox” type, you have to use the condition in an IF() statement. Additionally, you cannot simply chain the column and search for records where the value is true. Take this simple table:

17%20PM
Formula 1 CORRECT
If I want to answer the question listed on the screenshot: “Are Zack and Mr. Belding both male AND main characters?”, I originally thought this formula would work:

=if([Saved By The Bell].filter([Male or Female]="Male").[Main Character?]=true,"Yes","No not main characters")

However, the IF() evaluates to FALSE because the table returns an array of TRUE values (i.e. [TRUE, TRUE]. Even though both Zack and Mr. Belding are set to TRUE with their checkboxes checked, the array of TRUE values does not equal TRUE.

Formula 2 INCORRECT
Instead, if I use the CONTAINS() formula to check for a FALSE value, I can reverse the IF() statement values to correctly account for multiple records with their checkboxes checked (i.e. an array of TRUE values):

=if(contains(false,[Saved By The Bell].filter([Male or Female]="Male").[Main Character?]),"No not main characters","Yes")

2 Likes
2

Oh man I miss that show…
Can you say more about the scenario here? Would putting the checkbox true/false check into the filter() not solve this case? An if() would be needed to return a different string anyway, but the filtering could look like this:

https://cl.ly/0v2m2L2A2L1u/Screen%20Recording%202018-03-22%20at%2010.07%20AM.gif

True!

3

I just edited the question I was looking to answer from the data. The question I am asking is “Are Zack and Mr. Belding both male AND main characters?” I’m not looking to return the names that match these conditions, but rather return true or false (since this would impact future conditional formatting).