4 Replies Latest reply on Aug 29, 2014 10:04 AM by Matt Lutton Branched to a new discussion.

    Help with Filter Action Issue

    Matt Lutton

      Sample workbook attached.

       

      I have run into this problem a couple of times now, and so I thought I'd ask if you have a work around or a different approach to address this scenario.

       

      Basically, I often write calculations that return a count of student [ID] based on specified criteria.  I am using a distinct count of student [ID] to produce numbers (in this workbook, its the "Annual Review This Month", "Annual Review Next Month", etc. calculations.

       

      Once I've created measures based on those counts, I can display them easily enough in a bar chart.  However, I have been unable to find a good way to filter from the bar chart to a student list (which is commonly required in my work). 

       

      Can anyone take a look at the simple dashboard I've mocked up, and see if you can help me link the two together?  I want to return the students who match up with the bar chart above--so, students who are up for review this month, next month, etc.  should be displayed in the list below when a bar is clicked.

       

      I assumed since the student's ID is used in both sheets, it would automatically link them but I guess because I am aggregating the count of IDs on one sheet and simply displaying the dimension in another, it won't work as I expect it to.  In the bar chart as-is, each individual ID can be filtered down to and the bar is broken up into individual pieces, but I cannot filter for the entire bar (hopefully that makes sense).  I am more concerned about the entire bar and not the individual chunks that make it up.

       

      Any advice anyone can give on this type of scenario would be great.  I often write calculations to get distinct counts based on returned student IDs, so I certainly need a different approach if I can't link these to a student list.

       

      I really appreciate any help anyone can provide here.

        • 1. Re: Help with Filter Action Issue
          Shawn Wallwork

          How about the attached.

           

          --Shawn

           

          EDIT: Delete that action that ends in (copy 2), it was leftover and isn't doing anything.

          • 2. Re: Help with Filter Action Issue
            Matt Lutton

            Can you explain what you did here?  It is better now, but I'd prefer to click on the header of the bar or have the bar appear as one large chunk instead of having those individual pieces for each student ID.     

             

            I was able to replicate what you had done, but would like to understand what is happening here and why.

            • 3. Re: Re: Help with Filter Action Issue
              Jonathan Drummey

              In reading your goals, it seems like Tableau works differently than you are thinking that it does.

               

              What I'm hearing is that you have the student ID dimension, and you're thinking that since that ID is used in both sheets - as COUNTD(ID) in one sheet and ID in another - that you can easily go from one to the other.

               

              However, when you apply COUNTD() to a dimension like the student ID, that dimension is now a measure. This is subtle and critical to understand. When ID is aggregated in the measure, you no longer have access to the IDs, you only have the measure result - COUNTD(ID) that is aggregated based on the dimensions in the view.

               

              Here's an example:

               

              When ID is in the view as a dimension, then every ID (after filters have been applied) is available. The query that Tableau issues will be something like:

               

              SELECT ID

              FROM Students

              GROUP BY ID

              WHERE (filter criteria)

               

              When the COUNTD(ID) measure is in the view, then the query that Tableau issues is:

               

              SELECT COUNTD(ID)

              FROM Students

              WHERE (filter criteria)

               

              There's no individual ID available in the result, only the COUNTD(ID). Since I know this works in your view, I imagine that what you did when you wanted the Filter Action to work was to bring ID back into the level of detail. Now, Tableau automatically stacks marks for a Bar Chart, so the bar would look the same (except for the border shading). However, with ID in the level of detail the query that Tableau issues is something like:

               

              SELECT ID, COUNTD(ID)

              FROM Students

              GROUP BY ID

              WHERE (filter criteria).

               

              Tableau returns each ID and then does a Count Distinct on that, returning a value of 1. You can get a sense of this in the status bar at the bottom of the Annual Reviews worksheet, it is showing 1212 marks, one for every ID/Measure Names combination:

               

              2013-08-22 12_27_43-Inbox - Microsoft Outlook.png

               

              In the Annual Reviews bar chart, Tableau is drawing bars for 16 marks, one for each ID that has a review this month or next month:

               

              2013-08-22 12_38_31-Tableau - Filtering Problem SAMPLE-SW [Read-Only].png

               

              And then with the Filter Action based on ID in your original workbook, when the user clicked on a single mark they'd get the Student List for that single ID.

               

              What Shawn had done is to create additional dimensions based on your calculations - the Annual Review THIS MONTH/NEXT MONTH (copy)) fields - and put those on the Level of Detail Shelf in both worksheets, and made the Filter Action on those dimensions instead.

               

              This works, but still has the problem of multiple marks. The bar charts have the right values because Tableau's default behavior is to stack marks, even though the bar chart may be made up of a few or thousands of values. The solution is to reduce the level of detail in the view (i.e. reduce the number of combinations of dimension values). Using Shawn's workbook, we can take ID off the level of detail of the Annual Reviews worksheet. Now Tableau is only drawing 9 marks, one for each combination of values of the Annual Review THIS MONTH/NEXT MONTH (copy)) dimensions, and single bars.

               

              To put this another way, instead of thinking about filtering based on the finest grained level of detail (the ID), we're asking "What is the category (i.e. dimension) that connects this worksheet to the next?", and in this case the categorization is the previous/this/next month review status.

               

              I set this up in the attached.

               

              This points at the solution to getting the name of the month into header (without requiring any data reshaping), and that is to create a single dimension for previous/this/next month (by merging the separate criteria into one calculated field), then use that to create the rows and COUNTD(ID) for the measure. I can post a version of that later tonight.

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: Re: Help with Filter Action Issue
                Matt Lutton

                I thought I understood the difference between a dimension and measure, but I really appreciate you taking a look and responding Jonathan.  I'm the first to admit I don't always understand what Tableau is doing--so understanding something like this will only help me in the future.  I am learning more and more, and many thanks to you for your support and your willingness to walk me through these situations.

                 

                I considered a single calculation, but my experience with creating larger calculations is limited.  I would love to see how you would go about doing this--and the month names would be great, too!

                 

                I really cannot thank you enough for taking the time to thoroughly explain the WHY and HOW of things.