12 Replies Latest reply on Apr 3, 2014 1:10 PM by Tara Carter

    Filter multiple values

    Tara Carter

      Hi there,

       

      We have the original data as follows (the fourth row has multiple values)

       

      IndustriesDownloads
      Health10
      Health Plans20
      Border Management30
      Health; Health Plans; Border Management60

       

      Could we show in filter this way?

       

      IndustriesDownloads
      Health70
      Health Plans80
      Border Management90

       

      Any thought is appreciated!

       

      Thanks,

      Tara

        • 1. Re: Filter mutiple values
          venkat amalla

          Hi, Jonathan Drummey, Matt Lutton, Prashant Sharma

          This is looking interesting and challenging...

          Can you tell me is this possible in Tableau?

          • 2. Re: Filter mutiple values
            kettan

            The attached uses technique #5 in  The Cross Join Collection.

             

             

            SELECT l.[Industries]
            , d.[Industries] AS [Industries (source)]
            , d.[Downloads]
            FROM [data$] d, [lookup$] l
            WHERE d.[Industries] + ';' LIKE '%' + l.[Industries] + ';%'
            
            
            

             

             

            thread 141096 Filter mutiple values.png

            • 3. Re: Filter mutiple values
              Tara Carter

              Thanks Kettan!

              • 4. Re: Filter mutiple values
                Tara Carter

                Hi Kettan,

                 

                Besides the SQL query, do we have some functions (calculated field?) within Tableau that could the trick as well while keeping the data unchanged?  I asking this because our database is really large and there are multiple columns that have multiple values. If we separate them all by one row each, the database size will be extremely large. Say, 3 values in 3 columns each , it would be 27 times larger!

                 

                Thanks,

                Tara


                • 5. Re: Filter mutiple values
                  kettan

                  The only alternative I can think of is having a calculated field for each industry. Limitations with this approach:

                  1. Dimension filters don't filter industries because industries are measures
                  2. Dimension grand totals are not available because industries are measures
                  3. A calculated grand total formula shares format with the others (using capital letters helps a little)

                   

                  thread 141096 Filter mutiple values - Calculated field.png

                  1 of 1 people found this helpful
                  • 6. Re: Filter mutiple values
                    Tara Carter

                    Thanks for sharing the alternative, Kettan!

                     

                    Unfortunately, we do need the filter option. So there is no solution within Tableau functions that could both keep the multi-value cell intact and keep the industry as the filter?

                     

                    Thanks,

                    Tara

                    • 7. Re: Filter mutiple values
                      kettan

                      The good news is that you can also filter.

                      The bad news I was trying to describe is that the "measure dimensions" will be seen.

                      The comforting news is that they will be zero and can be formatted as empty.

                      We can hope some expert has a way around this although I don't expect so.

                      1 of 1 people found this helpful
                      • 8. Re: Filter mutiple values
                        Tara Carter

                        Thanks Kettan!

                         

                        What if we have multiple columns that have multiple-value celles? Could we create sets of calculated fields for each column and make each column be able to filter by single value?

                         

                        Please find another sample data attached. Our goal is keep the raw data intact (with multiple-value cells) while be able to filter by single value.

                         

                        Any of your futher thoughts is appreciated!

                         

                        Thanks,

                        Tara

                        • 9. Re: Filter mutiple values
                          kettan

                          MULTI-CHOICE PARAMETERS ARE CURRENTLY NOT SUPPORTED

                          Sadly Tableau currently only supports single choice lists for parameters.

                          Therefore I "workarounded" a single value parameter to "accept" multiple values separated by semicolon.

                           

                          YOUR SHARED EXCEL FILE IS VALUABLE

                          Thanks for sharing a very useful dataset with multiple "rows" squeezed into single rows. Although I used your original dataset (to save time), the latter dataset is an interesting example of why and when multiple "rows" are stuffed into single rows and believe Jonathan Drummey might like to take a look at it since this topic was "discussed" in  Re: Re: When I don't know the values beforehand how do I count the occurences of each value?.

                           

                          MY WORKAROUND

                          See the new formulas in attached workbook and below.

                          .

                          Health

                          IF CONTAINS([Industries] + ";", "Health;") AND
                            (CONTAINS([Parameters].[Industries] + ";", "Health;") OR LEN([Parameters].[Industries]) = 0)
                          THEN [Downloads] END
                          
                          
                          
                          
                          
                          

                          .

                          Health Plans

                          IF CONTAINS([Industries] + ";", "Health Plans;") AND
                            (CONTAINS([Parameters].[Industries] + ";", "Health Plans;") OR LEN([Parameters].[Industries]) = 0)
                          THEN [Downloads] END
                          
                          
                          
                          
                          
                          

                          .

                          Border Management

                          IF CONTAINS([Industries] + ";", "Border Management;") AND
                            (CONTAINS([Parameters].[Industries] + ";", "Border Management;") OR LEN([Parameters].[Industries]) = 0)
                          THEN [Downloads] END
                          
                          
                          
                          
                          
                          

                          .

                          GRAND TOTAL

                          IF CONTAINS([Industries] + ";", "Health;") AND
                            (CONTAINS([Parameters].[Industries] + ";", "Health;") OR LEN([Parameters].[Industries]) = 0)
                          THEN [Downloads] ELSE 0 END +
                          IF CONTAINS([Industries] + ";", "Health Plans;") AND
                            (CONTAINS([Parameters].[Industries] + ";", "Health Plans;") OR LEN([Parameters].[Industries]) = 0)
                          THEN [Downloads] ELSE 0 END +
                          IF CONTAINS([Industries] + ";", "Border Management;") AND
                            (CONTAINS([Parameters].[Industries] + ";", "Border Management;") OR LEN([Parameters].[Industries]) = 0)
                          THEN [Downloads] ELSE 0 END
                          
                          
                          
                          
                          
                          

                          .

                          thread 141096 Filter mutiple values - Multiple Filter.png

                           

                          Ps. The filter technique above is also shared in following threads:

                           

                          Filter a column that consists of a comma seperated list of words

                          Using a parameter and a filter on a string

                          1 of 1 people found this helpful
                          • 10. Re: Filter mutiple values
                            Jonathan Drummey

                            Hi Tara,

                             

                            I looked at your data and I can see why the some fields are loaded with additional "rows" of information. No matter the system you are using for filtering & sorting this data, fundamentally the data is storing multiple levels of detail in a single row and something will have to unpack those columns to get the desired results for filtering, and that process will introduce overhead. That functionality is not presently built-in to Tableau, and even the workarounds like using parameters run into additional limitations as kettan described, and can run into further limitations on performance in as data sets get into the hundreds of thousands of records on up due to the relative inefficiency of string comparisons and the fact that parameter-based views have minimal caching done. Also, if you want to take advantage of other features of Tableau such as table calculations, filter actions, etc. you are likely to run into problems if you don't reshape the data into a more suitable form for Tableau.

                             

                            You had noted your database is "really large", I've found that to be a very relative statement. I've helped users for whom "really large" is 100K rows, and others for whom "reasonable" was 1B rows, so how many rows are you talking about? The reason I'm asking is that doing something like using a cross join aka cross product to multiply the data (or a subset) of your data to get the desired interaction is not necessarily a problem for Tableau since it can handle very large volumes. Yes, it's an inefficient use of space, and making the tradeoff to get the desired interaction flow for the user might be worthwhile.

                             

                            I set up an  example in the attached where there are two data sources. One is your most-recently posted data set, with an Initial Row ID added. The other is a "scaffold" source that uses the cross product technique that kettan demonstrated to create a data set with all combinations of Row ID, Industry, Campaign, Country, and Operating Unit but no other fields. This way the cross product data set is kept to a minimal size.

                             

                            Then I built a view using Tableau's data blending where the scaffold source is the primary and the Raw Data the secondary, and the linking dimension is the Initial Row ID. This gives us full-on dimensions to use for filters from the scaffold, and no custom calculations are necessary for the view, we can even use Tableau's built-in Grand Totals. In addition, we can filter from dimensions that only exist in the secondary Raw Data source, however Tableau does always add in a Null value for those, in this view you can see the Null value in the Channel dimension.

                             

                            2014-04-02 10_35_00-Tableau - Book1.png

                             

                             

                            This does require extra work to set up and maintain, for example if you want to be partitioning a view by a dimension such as a Date then it has to exist in the scaffold source. However the scaffold can be completely dynamic and extremely fast to run, and get you the filter interaction you desire. If Excel is your data source, I'd suggest using a Tableau Data Extract for the scaffold source to keep it fast.

                             

                            Hope this helps!

                             

                            Jonathan

                            2 of 2 people found this helpful
                            • 11. Re: Filter mutiple values
                              Tara Carter

                              Hi Johnathan,

                               

                              Thanks so much for your insights and the guide!

                               

                              Some of our tables are up to 250K rows with multiple values. Those have been seperated by single values are more than 1B rows already.

                               

                              I think you and @kettan have lit up our way!

                               

                              Thanks,

                              Tara

                              • 12. Re: Filter mutiple values
                                Tara Carter

                                Thanks, Kettan!

                                 

                                Together with Johnathon's advice, I think we can see a good solution now.

                                1 of 1 people found this helpful