10 Replies Latest reply on Nov 27, 2018 5:46 AM by rafal.lipczyk

    Filtering data source no 2 by a value returned from data source no 1

    rafal.lipczyk

      I would like to be able to apply a data source filter on my data source number 2 based on a data returned from a data source number 1.

       

      Is this even possible?

       

      My data source no 2 returns the data aggregated at the team level however I am able to tell the team ID only if the data is returned by data source number 1 first (and subsequently filtered by using username() function).

        • 2. Re: Filtering data source no 2 by a value returned from data source no 1
          rafal.lipczyk

          Thank you Amit

           

          Yes, I could do that in the worst case scenario. Ideally I would like to apply a filter on the data source to maintain dashboard responsiveness and best performance.

          • 3. Re: Filtering data source no 2 by a value returned from data source no 1
            Amit Narkar

            it would be easy to help if you could provide with sample twbx file.

            • 4. Re: Filtering data source no 2 by a value returned from data source no 1
              Michael Gillespie

              Rafal, you can't do that.

               

              A data source filter is applied 1) To a single data source and 2) BEFORE you perform any workbook-level calculations.  You can't "chain" data source filters.

               

              Your options might be custom SQL, or perhaps a materialized view in your source database (if it's a database) or maybe something in Prep.

               

              Hard to say without seeing your data.

              1 of 1 people found this helpful
              • 5. Re: Filtering data source no 2 by a value returned from data source no 1
                rafal.lipczyk

                I can also describe how I want it to work:

                 

                Data Source One returns the data which looks like this:

                 

                advisor_idteam_idfield3field4
                1231xx
                1231xx
                3452xx
                3452xx

                 

                Once the data is in, the username() filter is applied on the data source limiting the data to the data set relevant to advisor viewing the report.

                 

                Data Source Two returns the data which looks like this:

                 

                  

                team_idfield2field3
                1xx
                1xx
                2xx
                2x

                x

                 

                All I want to do, is to use the team_id from the first query and apply it to the data source 2 as a data source filter.

                 

                Due to characteristics of the data I cannot join both queries neither in Tableau nor in the data source.

                • 6. Re: Filtering data source no 2 by a value returned from data source no 1
                  rafal.lipczyk

                  Thank you Michael

                   

                  Looks like data blending is the only option I am left with then.

                  • 7. Re: Filtering data source no 2 by a value returned from data source no 1
                    rafal.lipczyk

                    Once idea came to my mind though and it is based on the discussion I read recently about row level security and joining data sets at different grain of the data.

                     

                    If I added an extra column to the second data source, the one which returns data aggregated at the team_id level, and that column contained all adviser IDs that make each team concatenated and separated by a comma or semicolon and if I then used some CONTAINS type of function to create a filter which would return 1 if it was true that this concatenated string contained a string equal to USERNAME() function, I could then apply this to the second data source and achieve my goal replicating the impossible to do 'chaining' of data sources.

                     

                    Would this work?

                    • 8. Re: Filtering data source no 2 by a value returned from data source no 1
                      Amit Narkar

                      its still difficult to say without knowing some dummy data.

                      Perhaps this may help you.

                      Filter Data Across Multiple Data Sources - Tableau

                      • 9. Re: Filtering data source no 2 by a value returned from data source no 1
                        Michael Gillespie

                        This is where we have to be very precise about terminology, Rafal!  I apologize if the following is already clear to you, but just in case...

                         

                        A Data Source Filter is a very specific thing in Tableau: It refers to a filter that is applied to the source data in the Data Source tab of a workbook.  These filters are applied before ANY data is presented to a worksheet within that workbook, and the process to set up a Data Source Filter is completely manual.

                         

                        Any other kind of filter is NOT a Data Source Filter: it is a filter that is applied AFTER the data is retrieved from the root data source (MS SQL Server, Oracle, Excel, etc.).  These are commonly called Quick Filters in Tableau.  You could also refer to them as Worksheet Filters, I suppose, to differentiate them from Data Source filters.  The important thing to remember is that Quick Filters can only act on data presented to the workbook AFTER any Data Source filters have been applied.

                         

                        So, you cannot take the results of a Quick Filter and feed it back into a Data Source Filter.  It's just not possible architecturally.

                         

                        Having said all that, your best option is always going to be to address these kinds of issues before the data is brought into Tableau.  If you can't do that, then you'll have to do some kind of blend in the workbook.

                         

                        It's very hard to say whether your proposed approach would work or not without some actual data to play with.  Conceptually, I think it makes sense, but there could be something specific to your data that complicates things.  As Amit suggests, any way you can mock up some data for us to look at?

                        • 10. Re: Filtering data source no 2 by a value returned from data source no 1
                          rafal.lipczyk

                          I agree with what you have written above Micheal except the part where you say application of the Data Source filters is completely manual. That is probably true to most cases except at least one where you can use the USERNAME() Tableau function to filter the data before it is presented to the worksheet (filter is applied on the data source and it is dynamic as it will return different value depending on who the user is).

                           

                          Why do I care about the data source filters? If I am faced with returning 2 mil of rows vs returning 2k of rows, one can see why I really want to apply all my most important filters on the data source and avoid the blending or any other techniques that will slow my dashboard down and which are not efficient too.

                           

                          I will try to implement my concatenated strings approach as I am pretty sure I read about this solution (related to different but similar problem) some other place.

                           

                          From the link that Amit posted, the last part dedicated to the Source Fields and Target Fields would be sth I could apply too since my second data source, the one where the data is aggregated at the team level, is not as big as the first one so this will probably work too. A bit shame that the Tableau articles, just like in this case, often talk about things without explaining how to actually implement it so I am guessing here that all that would be required here is creation of a filter and specifying which worksheets should this filter be applied to, which in turn would automatically convert this filter to either source filter or target filter. It is a guess as there is no explanation on how to implement that concept.