12 Replies Latest reply on Jan 8, 2014 9:54 AM by Pankaj Chopra

    Many to Many relationship

    Pankaj Chopra

      Hello friends

       

      I have a similar problem like in the attached test file where there is a many to many relationship between a person and organization that is Each person can belong to none, one or many organizations and, of course, an organization can have any number of members as well. 

       

      In my filters I want an AND relationship for instance if I select two of the orgs only those members which below to both orgs show u?

       

      How can I implement this ?

        • 1. Re: Many to Many relationship
          Pankaj Chopra

          Does anyone has a solution for this or its a dead end?

          • 2. Re: Many to Many relationship
            Matt Lutton

            I believe this would be much simpler if your data was structured such that each customer had one row, with multiple columns for each organization type they belong to--which seems unnatural, since Tableau generally handles TALL data very well.

             

            However, I could be completely wrong and there is likely a way to get what you want without restructuring your data--I'm just blanking on this right now.  Hopefully someone smarter than me will chime in!

            • 3. Re: Many to Many relationship
              Pankaj Chopra

              Hi Matthew

               

              I am sure someone has come across this problem. Sounds like a common problem and an obvious one in many to many type cases.

              • 4. Re: Many to Many relationship
                Matt Lutton

                Certainly, they have--there are examples of these types of problems all over the forum.  Its just that it is pretty common to reshape data before bringing into Tableau as part of a solution, as well.  I'm not saying there isn't a way to do it--I'm sure there is but I have been unable to get the "usual" methods to work because the data structure won't allow us to do things like:

                 

                IF CONTAINS(Orgname, "Boston") AND CONTAINS(Orgname, "LongRoom") then Person END

                OR a filter like: CONTAINS(Orgname, "Boston") AND CONTAINS(Orgname, "LongRoom")

                 

                ...Because each person has three rows, rather than one.  I was unable to use sets or multiple parameters, as well.  But I may be missing something obvious here, too.

                 

                I just believe it would be much simpler re-shaped.  But, as I said, I could be totally wrong and way off-base.  I'll let someone else chime in before I go off trying to reshape the data for you.  I'm not an expert on setting up data sources--mine come to me pre-compiled for the most part.

                • 5. Re: Many to Many relationship
                  Pankaj Chopra

                  How can I reshape the data? Like put it in a cross-tab format?

                  • 6. Re: Many to Many relationship
                    Matt Lutton

                    There are a few ways--there is a Tableau Reshaping tool for Excel, but it typically takes Wide Data and makes it tall, so I'm not sure if it can be used for this.  Most folks use Custom SQL in Tableau, or the Database Admin will reshape it for them, if they're lucky.

                     

                    What I am proposing is one record for each Person in your data.

                     

                    But lets see if anyone has any other interesting approaches before going that route (assuming you aren't time crunched)

                    • 7. Re: Many to Many relationship
                      Michel Caissie

                      Pankaj,

                       

                      Have a look at the attached.  I used a combination of the Total() and index() function to filter the Persons that are members of all the Orgname selected.

                       

                      Michel

                      1 of 1 people found this helpful
                      • 8. Re: Many to Many relationship
                        Pankaj Chopra

                        Thanks Michael. Could you explain me conceptually what you did?

                        • 9. Re: Many to Many relationship
                          Matt Lutton

                          Thanks for posting this Michel Caissie!   +1 On an explanation of your process.

                          • 10. Re: Many to Many relationship
                            Michel Caissie

                            Still here,  i just posted yesterday just before leaving the office

                             

                            So the goal here is to show the persons where the count of Orgname is equal to the Count of Orgname Selected.

                             

                            If you look in the ValidateCalculation sheet you can see;

                             

                            -Count Orgname Selected:  this is obtained with the TableCalculation  TOTAL(COUNTD([Orgname])) computed using Table Down.

                            So every row gets a number corresponding to the Distinct count of Orgname in the Table. You can see this number change according to the number of Orgname selected in the quick filter.

                             

                            -index:  the index function is computed using  Person, Orgname  restarting every Person. So if the Person is member of all the Orgname selected, the max index will be equal to the value of [Count Orgname Selected]

                             

                            -index(MAX): not needed , i was just testing things

                             

                            -Person Filter:  This is use to identify the Person mentionned above.  if [Count Orgname Selected] = [index] then 1 else 0 end .

                            Using this as a filter (selecting the 1)  would be enough to identify the Person, but you would get the single row where [Count Orgname Selected]= [index] .  But if you what to show all the Orgname then you need the next Filter.

                             

                            -Person Orgname Filter:  WINDOW_MAX( [Person Filter] ) = 1  .  The goal here is to set at True all the rows in the Person,Orgname partition where the [Person Filter] have a 1. So for each Person,Orgname partition  if the MAX value of [Person Filter] is 1 the set the row at True.

                             

                            So in Sheet 4 i keep the Person and  Orgname on the Rows,  i need to keep the Person Filter on the row also because it is needed by the Person Orgname Filter , but i hide the header.  And I use the Person Orgname Filter  as the filter and keep only the True values.

                             

                            Michel

                            • 11. Re: Many to Many relationship
                              Matt Lutton

                              Thank you for explaining your process--very helpful. 

                              • 12. Re: Many to Many relationship
                                Pankaj Chopra

                                Thanks a lot Michel. Really appreciate putting your effort.