2 Replies Latest reply on Feb 26, 2012 6:52 PM by osman.javed

    Filtering the same data in multiple ways with circular dependency

      Hey guys was hoping I could get some help on a data set I'm working with. I've put together a pretty thorough explanation so please bear with me! I've tried several different routes now but haven't been able to get the desired result. As a note, I am using

       

      The Set Up/Structure

      1. The “Survey ID” table assigns each single survey a number. The first survey taken is number 1, and the 100th survey taken is number 100.
        1. Each number in this table represents an individual survey consisting of questions and answers
        2. For the sake of this example, there were 100 surveys taken
      2. The “Question” list contains a list of all possible questions
        1. For the sake of this example, there were 10 questions
      3. The “Answer” list contains a list of all possible answers
        1. For the sake of this example, there were 3 possible answers: Yes, No, and Maybe
      4. The “QA Reference” table is the intersection between questions and answers
        1. If you answered Question 1 with Answer 1, it would have a value “11”
      5. The “Survey Response” table is the intersection of all data: Survey ID, QA Reference, Questions, and Answers
      6. Note all of this information is stored in MS Access. I am  using a live connection and have created all of the Joins.

       

      Picture 1.jpg

       

      The Desired Result: Filtering

      I would like to filter my data in such a way that I can track how many people who answered Question 1 with a “Yes” answered Questions 5 and 6 with a “No”…. This means, I need to somehow store the Survey ID which uniquely identifies a person’s survey response. Meaning, if I filter on People who answered Question 1 with “Yes”, I want to see how the people who answered Question 1 with “Yes” answered Questions 2-10.

      Logic: “If Answer to Question 1 is Yes, store the list of Survey IDs. Given this list of Survey IDs, how were the rest of the questions answered?”


      The Issue: Data is Referenced

      Since all answers are being referenced from the Answer Table, I’m finding it difficult to filter. If I filter Question 1 to see how many participants answered Question 1 with “Yes”, I can no longer see the response “Yes” for any other questions. I’ve been using the Quick filter to filter my “Questions.”

      Ideally when I make a selection, I am selecting a list of Survey IDs which correspond to that question.

       

      Possible Solution: Have a new table (Storage Table) which can store the Survey ID’s applicable to my filter. This would be a dynamic table that changes based on my filter selection. Is this possible?

       

      Picture 2.jpg

       

      Do I need to create a new connection to the same data so that I can independently filter the data set w/o influencing the rest of the data?

       

      Any and all help is greatly appreciated! Sorry for the extremely detailed description

       

      The data is a sensitive so if I'm hoping this will suffice but if the tableau file is needed I can put something together.