2 Replies Latest reply on Sep 27, 2015 10:33 PM by garima.vatsa.0

    Apply a single date filter to two worksheets from different data sources

    Connie Gau
      • I have a dashboard with worksheets from two different data sources.
      • The data sources have a relationship.
      • I am able to filter the worksheet from the secondary data source with a date field from the primary data source.
      • I am also able to filter the worksheet from the primary data source with the same date field.
      • However, I cannot apply a single filter to the worksheets in the Dashboard. 
        • 1. Re: Apply a single date filter to two worksheets from different data sources
          Alfredo Pirrone

          Hi Connie

           

          I looked at your sample workbook but could not find a date to filter on your Geography Sample Data. Please have a look.

           

          However I think I know what you are experiencing because I have experienced this as well: I was not able to coordinate sheets in a single dashboard using a date filter when the sheets came from two independent data sources. I attached a workbook ("Crazy Filters") to demonstrate this issue. Notice that the sources share a common date field.

           

          One way to work around this is by defining a parameter and then filtering each data source based on the parameter. I attached a second workbook to demonstrate this solution ("Filtering with Parameter").

          The trick is to

          1) Create the parameter according to what you want to filter (in my example I choose a single year, and therefore I defined an integer parameter for this particular case. I called it [Date01 Parameter]). Establish reasonable values and format.

          2) Pick one sheet for each different data source and add a date filter on the common date field:

          2.a) Select  "Individual dates" to "How do you want to filter on [date field name]?"

          2.b) Click Next

          2.c) On the "General" tab, select "Use all"

          2.d) On the "Condition" tab, select "By formula"

          2.e) Create the formula that meets your requirements. In my example:

                    year([Date01]) = [Date01 Parameter]

          2.f) Test your filter by adding the parameter to the sheet (select Analysis->Parameters->[parameter name])

          2.g) If you use the data source in several sheets, check that the filter characteristics include "Apply to Worksheets" -> "All using this Data Source". In my example this doesn't matter as there is only one sheet per data source.

          3) Create your dashboard and add the parameter to use it as a filter (select Analysis->Parameters->[parameter name])

           

          Maybe someone else can contribute a way to make the linked data sources in "Crazy Filters" to work. Until then, you may want to try this solution.

           

          Hope this helps.

           

          Yours,
          Alfredo

          • 2. Re: Apply a single date filter to two worksheets from different data sources
            garima.vatsa.0

            Hi Connie,

             

            This is happening because you are using geography data source as primary data source in your first sheet and customer sales sample data source as primary data source in your second sheet. If you use same data source as primary in both the sheets then the filter will work fine and as i see your data you have postal codes or zip codes common in both the sheets on which you are blending the data so you can use zip code for building your map also as postal code also have the same data but using zip code will resolve the filter problem.

             

            Hope this helps.