Filtering on a value returned by CASE doesn't produce all the expected results

Looker Q&A Looker Studio
,
1

I have a table containing a text field, and then 4 bool fields (categories a through d). I have tried to filter which rows in the table I see, so I can choose to only see rows where e.g. category a is true, or category b is true etc.
In may data I have ensured that each category is true for at least one line item in the data.

I have created a calculated field called categories which contains:
CASE
WHEN category a THEN ‘category a’
WHEN category b THEN ‘category b’
WHEN category c THEN ‘category c’
WHEN category d THEN ‘category d’
ELSE ‘other’
END

When I use a quick filter (or a drop down) pointed at the categories dimension, I expect to see all the category names, however not all the categories appear, despite the fact that all categories should be returning true in the case function.

I added the categories to the table so I could check that they do in fact have lines returning true.

It doesn’t seem to be one category that’s the issue - if I add any of them to the CASE individually they work. I can’t seem to ever have more than 2 or 3 work at a time.

I’m new to Looker Studio so possible I’m doing something very naive. Any help appreciated :slightly_smiling_face:

2

It is key to understand how the formula you are including works, since it will return the value of the first category it finds, giving priority to Category A. In this way, if a specific record is true in category d and is also true in category a, it will return “category a”.

Could you provide an example of the values ​​you are analyzing?

3

Thank you - this was exactly the problem, I was looking for a way to return all the true options, rather than just the first. So, if a record is true in category d and also in category a, I was looking for a way to return category a AND category d.
I think I’m over complicating things, I can just use simple quick filters for now. Thanks for the help