1 Reply Latest reply on Aug 22, 2018 8:13 PM by albert wong

    Using Inclusions/Exclusions filters on a secondary data source

    Aaron Dobbins

      I am working on a cohort comparison where the user can select a specific customer, then select the cohort group based on a number of filters.

      After selecting the quick filters, they can view a scatter plot of the customer vs peers.  Here they can select one or more marks on the scatter plot and use exclude or keep only to remove outliers or choose a smaller subset of peers.

      I can then share those exclude/include filters on any sheet using the same data source (a graph for example) by going to the filter and using Apply to Worksheets > Selected Worksheets...

       

      I have another sheet in the same workbook that is based on a second data source where I can define matching fields to the primary data source and define the relationship as normal.  I can make the regular quick filters work on the secondary data source by using action filters, but I cannot figure out a way to make the include/exclude filters work. Maybe it is not possible?  If not, is there a workaround?

       

      Since the Include/Exclude filters are sets, is there a way to combine them or set up a calculated field to check if a record is in the sets and just pass that flag to the secondary data source?

        • 1. Re: Using Inclusions/Exclusions filters on a secondary data source
          albert wong

          Hi Aaron,

           

          It seems parameters may solve your issue.

           

          I made a dashboard using the superstore data.

           

          I started by creating a parameter and named it 'Category Parameter'.

          Data Type = Integer

          Display = Automatic

          Allowable Values =  List

           

          I then manually typed the Value of each category: 1 = Furniture, 2 = Office Supplies, 3 = Technology.

          (In your case, create a Peers Parameter and Customer Parameter. To group, use '1' for the names you want for Peers and '2' for Customers)

           

          Next Create a T|F calculated field 'Category Filter' referencing the parameter(s) below:

           

          [Category Parameter]=

          (IF CONTAINS([Category],'furniture') THEN 1 END)

          OR

          [Category Parameter]=

          (IF CONTAINS([Category],'office') THEN 2 END)

          OR

          [Category Parameter]=

          (IF CONTAINS([Category],'tech') THEN 3 END)

           

          Then bring Category Filter to Filters and select TRUE.

           

          Next, right click the 'Category Parameter' on the bottom left and select 'Show Parameter Control'.

           

          Now the (3) categories can be filtered on any spreadsheet as long as you bring that 'Category Filter' calculation to the Filters and selecting TRUE.

           

          Once you have your sheets on the Dashboard tab, select parameters from any sheet and bring the Category Parameter to view.

           

          This will allow you to filter multiple sheets with the rules created.