4 Replies Latest reply on Aug 26, 2016 3:53 AM by Łukasz Majewski

    Filter out all members of a dimension which don't contain the filtered attribute

    Andrew LaPrise

      I am so, so, overwhelmingly sorry for that astoundingly unintuitive question title.

       

      I've attached a workbook (V 9.1) with a dashboard containing two charts (using the Superstore data source). At the top, we have sales by Sub-Category. At the bottom, we have sales by Order. My goal is to show the orders which included the selected sub-category from the first chart. I do not want to exclude any lines from those orders, I just want to identify which orders contain the selected sub-categories. That was a pretty obtuse explanation, so an example is probably in order:

       

      Screen Shot 2016-04-12 at 11.18.28 AM.png

       

      That's the dashboard in question. Note that Order CA-2011-10090 contains lines for both Binders and Tables.

       

      Now I'd like to locate all of the orders that contain lines with Binders. So I select Binders from the chart above, and it (obviously) filters out all rows which do not have Binders in their Sub-Category:

       

      Screen Shot 2016-04-12 at 11.18.42 AM.png

       

      But what I'd like to see is each of those orders above (the orders with Binders), but with no lines filtered out, like this:

       

      Screen Shot 2016-04-12 at 11.20.17 AM.png

       

      (The above picture is canned, I just manually filtered out a few orders so I could demo my issue to you.)

       

      Is there a way to do this? Thanks for any help you can offer!

        • 1. Re: Filter out all members of a dimension which don't contain the filtered attribute
          swaroop.gantela

          Andrew,

           

          I couldn't quite get there, but maybe this can be a first step.

          I think what is needed is an action that targets the Order ID field.

           

          But it looks like that will require putting Order ID on the Details shelf,

          which will break up your bar into pieces.

           

          What I got working was that if you click on the Sub-Category

          heading of Binders, all Orders with binders and their other subcategories will show.

           

          The problem is that you can't just click on the Sub-Category bar,

          because it will filter the order worksheet to just one order.


          Will keep looking.

          1 of 1 people found this helpful
          • 2. Re: Filter out all members of a dimension which don't contain the filtered attribute
            Andrew LaPrise

            Hey swaroop.gantela,

             

            That certainly seems like the way to do it! (Great solution, thanks!) But you're right, I'd really like clicking the bar to work. I've tried a really excessive number of variations on that concept, including a gantt chart starting at zero masquerading as a bar chart, actually hiding your bar chart behind a second bar chart that doesn't have Order ID in the detail shelf, and using { FIXED [Sub-Category] : SUM([Sales]) } and turning off Stack Marks. Nothing yet has done the trick. (Happy to post any of those failed "solutions" if you want inspiration, but again, not even sort of successful.)

             

            I refuse to believe that I'm the first one encountering this problem, but I'm having a lot of trouble finding even a similar issue in the forums. Any other ideas you might have would mean the world to me! Thanks so much for the effort you put in!

             

            Thanks,

            Andrew

            • 3. Re: Filter out all members of a dimension which don't contain the filtered attribute
              swaroop.gantela

              Andrew,

               

              I'm still working on it.

              I think you were on the right track with the Self Join.

              I did an Inner Join with itself, and used the second version of the SubCategory as the Action detail.

               

              Essentially, for each SubCategory, it has the values for all other the other SubCategories.

              Which creates a problem for the Orders workheet, making it look like the table below.

              So I had set the filter to Clear out the Order sheet until a Sub Category is picked.

              There was also some stickiness in getting the summed value to show correctly.

              Furthermore, this Self Join may hinder the performance in your actual dataset.

               

              Regardless, hoping this can be a next step forward.

               

              OrderID
              SubCategorySubCategory1Value
              1AA2
              B4
              C6

              B

              A

              2

              B4
              C6
              ............
              • 4. Re: Filter out all members of a dimension which don't contain the filtered attribute
                Łukasz Majewski

                I was wondering if this problem can be solved with the new cross data source filters in tableau 10

                I tried to build something based on 3 ds duplicates when 1st is related with 2nd on Sub-Category, 2nd with 3rd on OrderID and thus 3rd with 1st on OrderID but this concept failed as action filters cannot be applied to 'all related ds'..

                Instead this kind of filtering may be achieved thorough the secondary ds quick filter but not with actions. 9.3 wb attached.