6 Replies Latest reply on Nov 11, 2019 8:24 AM by Tim Beard

    Filter dimension based on another dimension

    Mohit Goyal

      Hello Folks,

       

      I have my dataset arranged as Product Type and Product Name and sum of their sales.

       

      Screenshot 2019-11-07 at 7.39.17 AM.png

       

      I have placed the filter on Product Name.  What I would like to do is when a user filter on a Product name, the Dashboard should show other products within the Product type and their respective sales amount

       

      So if a user selects the filter on Apple, dashboard should show Sales of Samsung and Huawei as well under the Product type Mobiles.

       

      Right now since the filter is placed on Product Name, it is only showing the filtered product name and not all the products within the same product type.

       

      Screenshot 2019-11-07 at 7.39.28 AM.png

        • 1. Re: Filter dimension based on another dimension
          Tim Beard

          Hi Mohit,

           

          This looks very doable, probably using a parameter and set actions. Details once I've done it.

           

          Can you upload a copy of your dashboard (even a simplified version)? I'll try to build the function into it. Remember to upload it as twbx

           

          Just to be clear on the desired functionality, you wish to be able to select, for example Apple with the result being the table above, but only showing all products with the product type mobile. Correct?

           

          Thanks,

           

          Tim

          • 2. Re: Filter dimension based on another dimension
            Tim Beard

            Hi Mohit,

             

            I decided to just build it using the global suppliers database. This shows all manufacturers that have the same sub-category as the selected manufacturer, even if the manufacturer has products in multiple sub-categories. See attached.

             

            Steps I took:

            • Create Paramater to use as your filter, [Select Manufacturer Parameter]. The members are simple taken from the manufacturers field using the "Add from field" button
            • Create Calculated field to grab the sub-cat from the selected manufacturer, [Select Sub-Cat from ManufacturerCalculated field]:
              • IF [Manufacturer]=[Select Manufacturer Parameter] THEN [Sub-Category] END
            • Created a second calculated field to select all subcategories where the sub-category has any values of the first calculated field that are not null, [Show Manufacturer or not]:
              • IF [Sub-Category]={FIXED [Sub-Category]:MAX([Select Sub-Cat from ManufacturerCalculated field])} THEN 'Show' ELSE 'Hide' END
            • Drop this second calculated field into the filter and select only "Show" to be shown.

             

            One could probably also do some cool stuff with Sets! See Sets Appeal by Filippos Lymperopoulos here: https://tc18.tableau.com/learn/sessions/34636  Not needed in this case, though.

             

            Any questions, let me know.

             

            Tim

            • 3. Re: Filter dimension based on another dimension
              Mohit Goyal

              Fabulous Tim. Honestly I had given up on any solution.

               

              This solved my problem. Just one last thing can the parameter problem be used to filter other sheets as well?

               

              For instance I have Regions and  Employees data on other sheet which I want to0 filter from my Product Name column as well? Do I have to create a second calculated field for all other sheets as well as the 'filtered data' will be different than the Sub-Cat example you cited?

               

              Also while the solution works perfectly in your worksheet, when I tried to recreate it in my workbook example my data is not getting filtered and "Show" button is not shown in Filter option. I have attached the workbook.

               

              Thank you Tim.

              • 4. Re: Filter dimension based on another dimension
                Tim Beard

                Hi Mohit,

                 

                See attached. It works across multiple sheets and dashboards.

                 

                The three key things to note are:

                • You put the calculated field to show or not in the filter. And only that (at least with respect to this functionality)
                • You control the filter via the parameter control, i.e. you need to select "Show Parameter" from the drop-down on the parameter itself.
                • There is no "Product Filter" as such! I noted that you had put product in the filter panel and had shown this filter to control the sheet. Not needed as the filter function is handled by the parameter control (in lieu of the filter control) and the show/hide calculated field in the filter panel (in lieu of the product field)

                It's kind of a different way of thinking about the problem.

                 

                With respect to controlling the Regions and Employees sheet, I cannot say as you did not include this in the attachment. It depends on the structure of your data. If the data rows tie product name together with regions and employees, then yes. Or if the data tables can be linked to achieve this. I discovered that a lot of things in Tableau get much easier if you really understand your data. See my post Aggregation problems driving you mad? about my experience in learning this lesson.

                 

                Hope this helps you to implement in the full dashboard - please let me know!

                 

                Cheers,

                 

                Tim

                1 of 1 people found this helpful
                • 5. Re: Filter dimension based on another dimension
                  Mohit Goyal

                  Tim this is amazing. Thank you so much

                  • 6. Re: Filter dimension based on another dimension
                    Tim Beard

                    Hi Mohit,

                     

                    Glad it helped. I've learned so much from various generous contributors to these forums, it's great to be able to pass some of that knowledge on

                     

                    Good luck with the rest of the project!


                    Tim