Tableau - Filter via cell with multiple values

Question

So I have data like the below. If I add the example of state as a filter with the below data I'll simply have 3 options to select from. Instead I'm trying to create a filter that simply lists all available options.

Quick Edit: Yes I have considered using a Parameter, however I need the filter to be multi select which parameters do not offer.

State
NY
PA
FL
SC
NC
WV
TX
CA

ID | State
1  | PA, NY, FL, SC
2  | CA, WV, PA, NY
3  | NC, SC, TX, FL, NY

Second Edit:

I do not have the ability to reshape this data due to the potential number of options per column that I need to filter on, (75+ on at least two). Which is why I'm asking this question. I was hoping there might be a solution similar to SSRS where I can populate my filter with Query B and use the results to filter back to Query A.

Answer 1

In case someone finds this question and still is seeking the solution!

I had a similar need. The use case was a unique list of employees whose records each included a single, concatenated field of all of the cities they'd visited in the year. (The underlying data was correctly shaped with a distinct record for each city visited, but the client wanted a particular display view that worked great for them.)

So the client wanted a table that looked like this:

EMPLOYEE    CITIES VISITED
Person A    London
Person B    London,Paris
Person C    Geneva
Person D    Geneva,London,Milan

The easiest way for the client to find everyone who had visited London would have been to include a wildcard search filter. As soon as they typed London, the corresponding "Cities Visited" records would have appeared for them to select. However, the client wanted a filter option with preset cities they could click (rather than freehand entry of city names because they didn't want to guess what all the options were OR risk spelling errors).

Turns out it was very easy to do this using a parameter control, with many thanks to Dave Rawlings in the Tableau Community Forums (https://community.tableau.com/thread/210796)! I am not the OP from the post whom Dave assisted, but I was delighted to find it.

1) Create a list of unique options for the filter: I removed dupes in the underlying data to generate a unique list of cities visited. (The OP here would just use a list of states or state abbreviations.)

2) Create a parameter to create your filter options: - On the bottom left of the worksheet, create a new parameter. I called mine "Cities." - Type is String, Value Options are from a List. - Add your unique options one-by-one to the List. I also included an option called "All." - Set the Current Value to "All."

3) Create a calculated field to regulate the parameter. I named mine "CityFilter" and used this formula to set either "All" or the selected city to "Yes."

If [Cities]="All" THEN "Yes"
ELSEIF FIND([CitiesVisited],[Cities])> 0 THEN "Yes" ELSE "No" END

Here, "Cities" refers to the city the user has selected from the parameter list, and "CitiesVisited" refers to the concatenated field in the record with multiple/comma-separated values.

4) Create a filter from the calculated field: Drag the field to add to your filters: Set the required value to "Yes."

5) Display the parameter as a worksheet filter: Right-click the parameter to "Show Parameter." This adds it to the list of filters to the right of your worksheet.

6) Display the parameter as a dashboard filter: On your dashboard, on the upper right-hand side of one of your views, choose the Down arrow, then choose "Parameters." Select the parameter you created to add it to the filters list on the dash, too.

Reminder, as of now, parameters are single-select only, so the client only can filter to one city at a time! The multi-select, dynamic parameters Tableau are developing are focused on parameters across multiple data sources, rather than for multiple values within one field. That is because, in theory, this is bad data-ing. But if an example like this helps a client understand and leverage the data, I'm happy to oblige.

I hope this answers the original question for the OP or anyone else who could use a quick solution for a project!

Answer 2

I am not sure about your database design, Normally in a database there will be a table that will have all the values individually which will be used in the cases like the one you have.

I can think of a way may be a bit rough one but will work.

If you have access to database then take the state column in separate excel sheet.

Using text to columns make separate columns for all values and then pivot those to make single column.

Now in tableau using your original database and then join both using full join this will create a full set of all data.

Now create a filter with the column in excel sheet.

Hope this helps, but make sure to do a through QA as you are joining 2 databases

Answer 3

Reshape your data. Don’t have repeating lists in a cell.

In your example, reshape to have one data row describing each association of a state to an ID. You should have 13 Rows, each with one ID and One state.

Then analysis will be more straightforward. Regardless of tool. You can read about data modeling, database scheme design for more info. Your data violates first normal form. Try to achieve at least second normal form if possible