3 Replies Latest reply on Sep 9, 2016 5:18 PM by Clemens Briggler

    Filter based on one secondary data source field OR another

    Clemens Briggler

      Hey all,

       

      I've looked high and low for the answer to this question with no success. I'm using Tableau 9.3. Here's the set up.

       

      Data Source 1 (Primary)

      Item

      Primary Category

      Secondary Category

       

      Data Source 2  (There can be many subcategories per Category)

      Category

      Sub-category

       

      The categories in data source 2 are used in both primary and secondary category fields in data source 1.

       

      Given a sub-category, I want to filter items in Data Source 1 where the sub-category is in the Primary Category OR the Secondary Category.

       

      Any advice is appreciated.

        • 1. Re: Filter based on one secondary data source field OR another
          Shinichiro Murakami

          Clemens

           

           

          Here is my approach.

          Set 2nd data as primary data source.

          And duplicate 1st data source.

           

          One is for P category and make relationships with Category and P category

           

          Another is for S category and make relationships with Category and S category.

           

           

          Then put formula as below.

          [Category Lookup 1]

          ifnull(ATTR([Primary P Category].[P Category]),ATTR([Primary S Category].[S Category]))

           

          Also put P category from P category data source and S category from S category data source.

           

          Then hide from Header.

           

           

           

           

          Thanks,

          Shin

          • 2. Re: Filter based on one secondary data source field OR another
            Clemens Briggler

            Shin,

             

            Thank you so much for your reply. This gives me a lot to think about. I see what you are doing with joining both the P set and the S2 set to the new "primary" S.

             

            Unfortunately, I'm not sure this is the best solution for my situation. In my scenaria, the primary data source has a lot more fields and needs to remain as the primary.

             

            Thanks for including the tblx file. I will study it further and see if it can help me create the final solution.

            • 3. Re: Filter based on one secondary data source field OR another
              Clemens Briggler

              Figured it out. Turned out to be pretty simple. (It always is once you find the answer.)

               

              Data Source 1 (Primary)

              Item

              Primary Category

              Secondary Category

               

              Data Source 2  (There can be many subcategories per Category)

              Category

              Sub-category

               

              The categories in data source 2 are used in both primary and secondary category fields in data source 1.

               

              Given a sub-category, I want to filter items in Data Source 1 where the sub-category is in the Primary Category OR the Secondary Category.

               

              Solution:

              Create a third data source using Data Source 2 (we'll call it Data Source 2A)

               

              Blend primary to Data Source 2 on Primary Category = Category

              Blend primary to Data Source 2A on Secondary Category = Category

               

              Create a Parameter called SubCatParm based on [Data Source 2].[Sub-category] that contains all of the sub-category values. Make this parameter visible on the sheet.

               

              Create a calculated field in Data Source 2 called InSubCat2. Formula: [Sub-category] = [SubCatParm]

               

              Create a calculated field in Data Source 2A called InSubCat2A. Formula: [Sub-category] = [SubCatParm]

               

              Create a calculated field in Data Source 1 called ItemInSubCat. Formula:

                    max([Data Source 2].[InSubCat2])=TRUE

                   or

                   max([Data Source 2A].[InSubCat2A])=TRUE

               

              Filter sheet on ItemInSubCat = TRUE

               

              That's it.

               

              When you select a Sub-cat value for SubCatParm, the secondary source calculated fields update to true or false accordingly. ItemInSubCat returns True if the Sub-category selected is related to either the primary or secondary category.