1 Reply Latest reply on Apr 26, 2018 4:08 PM by Jennifer VonHagel

    Filters on field but keep all records that contain item with filter

    Daniel Adorno

      Hi All - tried to find a similar thread but had no luck.  I'm trying to filter a data set like I have in my example below, so that if you were to filter on "SPORT", all records (Cities) that contain at least one like from B are kept.   So for example...

       

      Data:

       

      Row; Ctiy;  Sport

      1; Chicago; Hockey

      2; Chicago; Baseball

      3; Chicago; Basketball

      4; Boston; Baseball

      5; Boston; Racing

      6; Miami; Hockey

      7; Miami; Baseball

       

      Example, if i have a 'Multiple Dropdown' filter on SPORT and select only "Hockey", then rows 1,2,3,6 and 7 would still show.  Rows 4 and 5 would be filtered out since "Boston" doesn't have hockey listed.  If in my 'Multiple Dropdown' filter I choose "Hockey" AND "Baseball" then NO filter would be applied, since all cities have at least ONE of those 2 sports.

       

      In SQL I'd be using a DISTINCT in some fashion to accomplish this, but can't figure out how to do this in Tableau.   I'm trying to avoid having to use joins since with the project data I have, I can have the equivalent of up to 50 unique "sports" and 500k+ rows of data, and don't want to duplicate the information.   Any help would be greatly appreciated.  Thank you in advance for any help!

        • 1. Re: Filters on field but keep all records that contain item with filter
          Jennifer VonHagel

          Hi Daniel, I know you want to avoid a join, but how about a blended solution? 

           

          Given this as your main data source (and I'm guessing there are many more columns, with measures and maybe more dimensions):

           

          If you take only non-duplicate records of the City and Sport columns as a secondary Lookup source (the equivalent of "select distinct City, Sport from table"):

           

          You can use Sport from the Lookup source as a filter, and let an action filter apply this at the City (rather than the Sport) level.

           

          1. Add the main Table data source and the Lookup data source separately to Tableau - don't join them.

          2. Let the data source relationship join on City and Sport.

           

          3. Create your main table to be filtered - these are all fields from the main data source:

           

          4. Create a list of Sports from the Lookup Table, and put City on Level of Detail. This will be your filter:

           

          Put these two objects on a Dashboard, then set up an action filter.

          In the Action Filter, manually set the Target Filters Selected Field to be City only (not Sport).

          Then you can click on one or multiple values in the Sport Lookup Table, and it filters the main Table:

          Hope this helps,

          Jennifer