Need fancy filter logic? AND/OR filters are the answer!

[mannybhatia]

Are existing Looker filter options holding you back when identifying cohorts of customers, building campaigns, or working with customer segmentations? Do you end up writing a not-so-user-friendly custom filter expression because the default filters don’t have the mix of AND and OR you need? 

The new ‘AND/OR Filters on Explore’ labs feature is the answer! A new experimental user-interface is available in Looker release 23.10 that allows users to create complex filter logic involving using both AND and OR filter conditions. 

With built-in AND|OR filters, use cases like Campaign Builder and Cohort Analysis are quick to implement. Analysts will be able to select precisely the data they need by defining filters that combine both AND and OR conditions directly in the Explore UI. Power users can combine their custom filter expressions with the new AND|OR options in the same filter UI. As with any complex filter logic, you can test what you build in the SQL tab of an Explore page, to prevent unexpected results.

To test the new feature, the Looker Admin of your instance can enable the AND/OR Filters on Explores experimental flag on the Labs page in the General section of the Admin panel

The AND/OR Filters on Explores experience is currently only available in Explores, but users can save the filtered content to a new or existing Dashboard. AND/OR Filters, like custom filter expressions, cannot be defined on dashboard filters, but will still be applied to the underlying query.

What’s next? We want to bring the same filter capability to the dashboards as well. If you have any use cases, or example usage patterns that you want us to consider, please open a feature request using the ‘Product Idea’ option in Looker.

[Lauren_vdv]

Thanks for sharing @mannybhatia - great to see this new feature! 

[chiprut89-16538]

Amazing! 

[betul_a_korkmaz]

Love the new feature however it is only available for some dimensions/explores. In what cases is the option to toggle between AND/OR not possible? (In the same explore filter some dimensions can change between AND/OR others can't.

[mannybhatia]

'required_filters' and 'conditional_filters' can only be used with an AND operand between groups. Users can create a new group, and add one or more filters conditions with an OR operand within the group.

You cannot use an OR operand when combining dimensions and measures as filters ( based on SQL rule) 

[ben_davis-16359]

I really like having this as an option.  However, we did not exactly "Know" this was being rolled out with this release and it cause a bit of confusion with our users.  I can see this feature mentioned in the release notes however it can be challenging to actually understand how a change will impact users based on 2 sentences.  Are there any options available that allow administrators to get a preview of the upcoming release changes before they are released as kind of a live walkthrough?  Being better prepared to communicate changes to our users really makes a difference in how they perceive Looker.

[mannybhatia]

Hey Ben, We had conducted a BICC session back in June to let customers know, but I agree that we could have done more to let users know of upcoming features. 

[Dawid]

I had no idea this has been released as well. I pay close attention to Release Notes (even have RSS feed), in order to know about such features before my stakeholders discover it and ask me what the heck is going on and how it works..

[Dmitri_S]

Hey @mannybhatia 

Can you review why "OR" statement between measures is not allowed? I guess it should be a straightforward condition within SQL HAVING statement, no?