2 Replies Latest reply on Oct 14, 2012 3:39 PM by Nay Lin Soe

    Filter of one data source applying on another data source

    Nay Lin Soe

      Hello,

       

      I've got a question and hope you could shed some light on it for me. Suppose I have got two copies of a same data source, connected to a single workbook. Essentially, I like to find out if it is possible to apply a quick filter on one of the data sources that will affect on the other data source as well.

       

      In the attached example, the goal is to keep Selection/All field value to 1 at all time. There are 3 sheets:

      • In the 1st sheet (Without Date Dimension), when date filter is applied (i.e. not 'All'), Selection/All is not 1, which means the date filter is not applied to the other source.
      • In the 3rd sheet (With Date Dimension: Month), the goal is achieved. But it requires adding date dimension (in month), create grand total and hide all other values.
      • Why the 2nd sheet doesn't work, I don't understand.

       

      My question is: isn't it possible to achieve my goal without having to add the date dimension at all? Thank you for looking at this for me. Have a nice weekend all...!

       

      Regards,

      Nay

        • 1. Re: Filter of one data source applying on another data source
          Mark Holtz

          Hi Nay,

           

          In Tableau, you cannot apply a filter across databases.

          Although it seems like that what is happening in your example on sheet 3, it is not really working how you want...

           

          The reason that your Selection/All value of 1.00 (which is based on measures from both data sources) changes when you filter is because you are filtering only 1 source.

          Say I deselect May 2012 on the global filter you have set.

          When your view includes only the YEAR value (as on sheet 2), one source reflects the sum of the filter selection only (1+2+3+4+0) but your other source reflects the total for the YEAR for all your records (1+2+3+4+5). The result is 10/15 or .67

           

          I believe the reason the 3rd tab looks as though it is filtering is just because of the join being enforced.

          So when you May 2012 is deselected, the first source gets filtered (1+2+3+4), but Tableau do not find any value to add to the sum from the second data source (1+2+3+4+???)--so this is not happening because the second source is being filtered (1+2+3+4).

           

          If you look at the Relationships under "Data", "Edit Relationships" you can see that you are enforcing a join at the MONTH(Date) level. 

          You can remove MONTH(Date) from the Relationships list, and you will again see the .67 show up (1+2+3+4) / (1+2+3+4+5).

           

           

          All that said, there IS a work-around--sort of.

          You can create a parameter if you absolutely must use 2 data sources that cannot just be combined into 1. (You will probably want to endeavor to not have to use a second source.)

          In the event that you must use a second source, you would create a parameter which holds values that should be filtering each data source (Date).

          I'd suggest you make it a list because if your users are left to enter any value they wish, their might not be any data which matches the value.

           

          You would then create a calculated field in EACH data source that will basically say:

          IF [Dimension] = [Parameter]

          THEN give the value you want

          END

           

          You can then create a calculation using each of these "filtered measures" that will achieve what you want.

          I attached your workbook with the parameter filtering.

          The drawback is that you cannot make parameters 'multi-selectable.' Furthermore, parameters do not dynamically refresh their members (so if you added June 2012 to your data and you have established a "list" type, your parameter would not reflect the addition--you'd have to update the parameter selection members list manually).

           

          Sorry I couldn't give a better answer for you, but I hope this is helpful!

          1 of 1 people found this helpful
          • 2. Re: Filter of one data source applying on another data source
            Nay Lin Soe

            Thanks Mark. I can see and appreciate the time you must have put into this reply. I should have provided more information to save you some typing.

             

            Actually I am aware of the use of parameter as a walk-around. The reason why I can't be satisfied with it is, as you've pointed out, it requiring updating periodically to reflect the new dates. Unfortunately, I have to create 2 copies of the data source.

             

            What I still don't get is why sheet 3 should work and NOT sheet 2. You have pointed out that the relationship is done on MONTH level and suggested removing it (as you have done in your re-attached sheet which makes both sheets behaving in the same manner).

             

            However, in the relationship that Tableau generated automatically, it was on every aspect of the date in which both MONTH and YEAR, among others, are considered. As such, I am still at a loss as to why (in my original sheet) one should enforce a join that results in a calculation that is different from that of the other. (Shouldn't they perform consistently?)

             

            Once again, thank you Mark.

             

            Nay