3 Replies Latest reply on Aug 3, 2018 9:03 PM by Ankit Bansal

    Filtering on a One to Many relationship

    Todd Cheever

      So I need to use a hypothetical due to the nature of the data I'm working with...


      So in theory if you want to filter information based on a one to many relationship how would you do it?


      Here is my issue:


      I have a sub-set of data that has one unique identifier which is associated with many other records. For this example say


      Person A - Has three rows associated with them C,D,E

      Person B - Has only two of those aspects D,E


      How can I create a filter that will show me ONLY person B in my query results since that person is missing the criteria I'm searching for.

        • 1. Re: Filtering on a One to Many relationship
          Michel Caissie



          Without having any data to confirm my hypothesis, you can try this.

          If I understand correctly , you want to filter  D and E in,  and only show PersonB rows ,  and not PersonA's D and E rows.


          Put the Dimensions  Person and Aspects on the Rows shelf.

          Put Aspects on the filter.


          Create a calc PersonCount that is


          compute using  Person, Aspect restarting every Person


          Create a calc   SelectedAspectCount


          compute Table down


          Create a calc  isPersonHavingAllSelectedAspects

          SelectedAspectCount =  PersonCount

          Put this on the filter shelf and keep the true values.


          If it doesn't work, please mockup a tableau packaged workbook with false data, it will be easier to troubleshoot



          • 2. Re: Filtering on a One to Many relationship
            Todd Cheever

            Thank you for responding!!! So I think an example of the data and desired result will get us there.


            Essentially I have a subset of fund data where every fund might have multiple "fee categories" I'm looking to create a report that yields exceptions IE funds that don't have a specific fee category in the system.


            So In the example data tab you'll see a simplified example. In this case "FUND A" has an admin fee, a mgmt. fee, and an expense ratio. "FUND B" has both Admin and MGMT fees but no expense ratio.


            The final report I honestly don't really care what fee categories exist. I want to sweep my data for funds that don't have a specific fee category and return only the list of funds that are missing "EXP_RATIO"


            When I use the standard filtering criteria in Tableau it filters out the fee category rows but not the funds that have those categories. It isn't yielding a report consistent with what I would expect to see.

            • 3. Re: Filtering on a One to Many relationship
              Ankit Bansal



              Create a calculation field as :


              {fixed [Fund Name] : max(if [Expense Type]='EXP_RATIO' then 1 else 0 end)}=0


              Put it on filter shelf and choose TRUE.


              Hope it helps.