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

          Todd,

           

          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

          Window_Max(index())

          compute using  Person, Aspect restarting every Person

           

          Create a calc   SelectedAspectCount

          size(Aspects)

          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

           

          Michel

          • 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

              Todd,

               

              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.