You can always use the quick filter wildcard tab and then contains option on the Favorite cars field.
2 of 2 people found this helpful
This is a challenging problem for Tableau for two reasons:
1) The raw data has a multi-valued field. From a database standpoint (and Tableau works with data like a database) these are fairly terrible constructs because they break notions of one "cell" or "tuple" meaning one thing, instead that one entry has multiple meanings.
2) The way Tableau's categorical quick filters work is that for a given field they are an OR filter. So selecting BMW and Ford would return all Name/City combinations that have BMW, Ford, or BMW and Ford. From your description you're wanting an AND filter, so selecting BMW and Ford would only return customers with both.
The workarounds that I've used are:
a) Figure out the maximum number of simultaneous selections a user might want to make. Then build a parameter with the list of values, duplicate that parameter N-1 times, and build a calculated field for the filter that uses all of the parameters with a formula like CONTAINS([Favorite cars], [param1]) AND CONTAINS([Favorite cars], [param2]) and so on. This doesn't require any data prep but isn't a great user experience.
b) Split the field to multiple rows, for example the first original row with John/London would have rows for BMW, Audi, and Volvo. We can't do that operation in Tableau Desktop, you'd need to use Tableau Prep or some other tool like Alteryx or PowerQuery. Then write a set of calculations using LOD expressions to determine how many values selected, then use additional calculations to determine how many were selected for each person and then compare the two to figure out if they'd selected enough. Besides the extra work for data prep this can fail when the data is sparse.
c) I haven't used this one yet in production...v2018.3 (in beta) has a new feature called Set actions. With the split to Rows we can use a Set Action instead of a quick filter to get the results (I can share a demo workbook of this if you like). The issue with this is that we need an origin worksheet for the Set Action instead of the multi-select Quick Filter control and if the list of values is long it can be more challenging to work with.
first I want to thank you for taking the time to write such a detailed answer. I will explore all the options and post an update here with the outcome.
But thank you again, this will probably help others facing with a similar dilema.