3 Replies Latest reply on Oct 15, 2016 9:41 AM by Robert M Chapple

    Create a filter based on substring of Dimension

    Robert M Chapple

      I have a Dimension with multiple components & I want to be able to create a filter that allows me to select each one individually.


      In my example, I’ve got the movies of Robert De Niro with their Genres (taken from IMDB). These can be from one to three words in length (e.g. ‘Drama’ to ‘Comedy, Crime, Drama’). I reckoned that if I created a calculated field that checked for the presence of the Genre as a substring I’d be able to use it as a quick filter (here called Genre Type to distinguish the two)


      IF CONTAINS([Genre], "Action") = true Then "Action"

      ELSEIF CONTAINS([Genre], "Adventure") = true Then "Adventure"

      ELSEIF CONTAINS([Genre], "Animation") = true Then "Animation"

      Etc …


      It has worked in so far as it has taken the first Genre in the list and used that as the overall Genre. So, I now have Genres listed as Action, Adventure, Mystery, etc. … but not ‘Sci-Fi’ or ‘War’, neither of these are the first terms in the overall Genre description. Basically, I have eight genres to filter on, rather than the 19 there should be.


      As always, any help and assistance would be much appreciated!



        • 1. Re: Create a filter based on substring of Dimension
          Tom W

          Your best bet would be to preprocess your data so you end up with a 'Movies' table and a related 'Genres' table with a many to many relationship between them. In other words, each move could have one or more related Genre rows. That would allow you to pull the Genre dimension in and you'd be all set.


          If you don't want to or cannot go down that path, you have a couple of options;

          1. Create a parameter which is a list of genres. Drag your existing combined 'Genre' field to the filter shelf, click the Condition tab, select 'By Formula' and enter CONTAINS([Genre],[paramaterGenre])
          This will filter your genre string where a match is found with the genre parameter.


          The limitation with this approach is that it's single select.


          2. A similar question came up yesterday. There are some links contained within with some methods you might be able to utilize yourself - Filter values in multiple columns . Effectively the wildcard search might be the next best bet but the obvious downside being the user has to type the genres rather than select them.


          While it's the most work, I think pre-processing is really the only true way to get what you're

          1 of 1 people found this helpful
          • 2. Re: Create a filter based on substring of Dimension
            Robert M Chapple

            Thanks Tom!

            Couple of interesting ideas there. I'm away for the next day or so (attending a Tableau 10 Roadshow in Dublin), so I'll have to wait before I have the opportunity to try them out ... I'll let you know how I get on!



            • 3. Re: Create a filter based on substring of Dimension
              Robert M Chapple

              I just wanted to say Thanks again Tom W ...

              It took a bit of time to get back to this, but I finally put it all together & used your solution to break the Genre data out into a second table ... a bit laborious, but worth it ...