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.
it would be easy to help if you could provide with sample twbx file.
1 of 1 people found this helpful
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.
I can also describe how I want it to work:
Data Source One returns the data which looks like this:
advisor_id team_id field3 field4 123 1 x x 123 1 x x 345 2 x x 345 2 x x
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_id field2 field3 1 x x 1 x x 2 x x 2 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.
Thank you Michael
Looks like data blending is the only option I am left with then.
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?
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?
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.