How to display filters with optional relationship(OR or AND)

Question

We have a table of users and at the top I have 8 buttons, each one representing a filter (age, status, role...). When clicked it displays a dropdown with multiple checkboxes. The relationship between the filters is AND by default but it's OR between the checkboxes (we can select both females and male etc.). What I am trying to do now is have an option where I can switch the relationship between 2 or more filters from AND to OR (show me all females OR status_active). The result should give back all active users and all females (including inactive ones). My problem is how to display it? How do I add this or option to each filter and make it toggled (it should be specific for each filter and not a global relationship between all 8). What is the best way to implement my desired functionality without leaving the page crowded and hard to understand?

Answer 1

User understanding of the difference between Boolean ANDs and ORs is a perennial usability stumbling block. I’ve seen programmers confuse ANDs and ORs even when using their own design. Fortunately, users seem to do okay with your initial approach where multiple values for the same attribute (e.g., Status) join with a Boolean OR while multiple values of different attributes (Status and Gender) join with a Boolean AND. Furthermore, this seems to suit nearly all usage, and is enough for many applications. Worse case, if users want records for Gender: Female OR Status: Active, they do two separate filters sequentially and view the results separately. Yes, there’re be overlap in the results, which means extra work, but it’s possible to do the job, and maybe that's good enough if it’s rarely required.

If you must support more flexible Boolean operations, it helps that you don’t want to allow joining multiple values of the same attribute with a Boolean AND since that logically leads to zero results (e.g., a single mutual fund cannot belong to more than one Risk Category). So all you need to do is support joining multiple values of different attributes with either AND or OR. Ideally, the UI would make typical filtering easy, while only the more unusual filtering (like ORing values of multiple attributes) requires additional skill.

In my testing with users, the easiest way to make this understandable is with “query by example” (QBE). Get rid of all your buttons at the top. Instead, have a toggling button that puts the table below into filter criteria mode. In filter criteria mode, you clear the table and instead show the filter criteria values for the attributes (columns), as if you’re presenting example(s) of the kinds of resulting rows the user gets. Blank values imply any value may appear. Users can change the criteria values with text entry and dropdown controls just like any edit-in-place grid.

Here’s the filter for Risk Category: Aggressive.

Here’s the filter for Risk Category: Aggressive AND Asset Class: Equity.

Each row in the result will have Asset Class of Equity and Risk Category of Aggressive.

But here’s the filter for Risk Category: Aggressive OR Asset Class: Equity

There are rows with Risk Category: Aggressive where Asset Class can be anything, and also rows where Asset Class is Equity and Risk Category can be anything.

You can permit specification of multiple values within an attribute by similarly using multiple rows:

But I would recommend you continue to use dropdowns with multiple checkboxes, so users can enter multiple values in the single cell:

This doesn’t seem to cause confusion, and means users can do the most common filtering without learning how to use multiple rows.

In fact, I’d recommend you include a toolbar in filter criteria mode to “insert” various operators within a single cell

QBE can be expanded further to include advance abilities like subqueries and joins for more expert users.

From my observations, QBE is easier to understand than the nested/hierarchical query-builder UIs. QBE has advantages over those other approaches in that:

• Simple filtering is relatively simple; to filter on one attribute value, it’s Click into Filter – Enter Value – Click out of Filter.
• Users don’t have to understand what the words “and” vs. “or” mean in this context (or “Any” vs. “All”, which doesn’t make it any better from what I’ve seen).
• The attributes for filtering criteria are in the same place as the attributes for results viewing, so users don’t have to reorient themselves to find the attribute they want to filter on.
• Users have a good sense on where to start since they’re basically filling out a form.
• It's not possible to enter a nonsense filter where the multiple values of the same attribute are joined by a logical AND.

You also should construct a human-readable string representing the current criteria, which you display on top of the table when not in filter criteria mode. Users can then see their current filter setting when viewing the results.

The main challenge is making it clear whether the user is in filter criteria mode or not, and how to get in and out of it.