The secondary will only be filtered if there is an (explicit or implicit) relationship on Region.
It's a bit hard to say without an example, but it sounds as if you need to put Region on Level of detail and then you may need to use table calculations to aggregate back up to the level you really want. Post a demo showing what you're after.
Richard, I threw a quick example down. The two data sources have relationships established on Group and Region. The first tab has both Group and Region in view and all numbers are computed correctly. The second tab does not have Region on any of the shelves. Region is on the filter shelf. If I set the filter to "CA" for example, the main data source will be filtered correctly (group = West and July sales = 187,500) BUT...the 3 months Avg from data source 2 is only filtered down to the group in view (West 3 months avg = 657,000). The CA filter did not get computed in data source 2 because the Region field was not on the row or column shelf.
If my real application was as simple as the example I built, I would not have to blend data sources. I could have it all in one table or I could use SQL joins. Unfortunately, I do not have a choice. My real primary data source charts numbers over a businesss day axis. My secondary data source brings averages (weekly, monthly, yearly, etc...) to the view. Those need to be flat lines across the chart. If they were coming from the same data source or through SQL joins, it would give me a data point for each business day instead of a flat line (see attached picture).
Hence, the need for the blending and my frustration that I cannot pass a filter from the main to the secondary data source unless the filtered field is in view.
You need to post the .twbx.
I noticed your still on 6.0 - I only have 6.1 on my machine, so I'll only be able to post back a 6.1 workbook - do you also have 6.1 available? If not I can just describe anything I want to comment on...
I added a couple of sheets which hopefully show what's going on with blending and how to get what you want with Level of Detail and table calcs.
I selected multiple regions to make it clearer what's happening. Note the filter to the first row of the partition so we only get one row in the view, despite having multiple rows of detail returned from the data blend.
Any chance you can upload a version in 6.0? We haven't upgraded to 6.1 yet, so I can't open your workbook.
My laptop blew up and got rebuilt the other day, so I didn't bother to reinstall version 6.0.
I'll see if I can dig out the old installer for you...
I jumped on the 6.0 version since that's what I am running but I keep getting an exception error that shuts Tableau down when I click on the last tab. I will install 6.1 today as I can't wait to see what's on that tab. I will let you know if it can be adapted to the complex workbook I have in mind. Thanks!
Richard, I got it to open in 6.1. This is definitely working but I am struggling a bit to understand the concept of the first row and partition. Definitely confused by the need to use the Total() function in calculated fields rather than the existing measures. I see what it is doing but I am not intuitively grasping the whole concept....
One thing is sure, it drastically slows the process down in my workbook with about 150,000 records in my primary data source and 130,000 in the secondary one. I am connected live to SQL tables and I am building extracts as I type this to see if it will improve the return times.
Will explain a bit later - just dashing to a meeting...
Will also think about the performance - but no immediate brainwaves. ;-(
By putting [Region] on the Level of Detail shelf what we are doing is getting Tableau to retrieve the following rows from the database (with the filters set as I have them):
Group Region July Sales 3 Months Avg
EAST MA 101,250 135,000
EAST NY 123,000 164,000
West CA 187,500 250,000
West CO 78,750 105,000
I am then using a table calculation to add up the two measures over the [Group] partitions. So wrapping the SUM()s in TOTAL()s results in these rows (note that the TOTAL()s give the same answer for each row in the partition):
Group Region TOTAL(July Sales) TOTAL(3 Months Avg)
EAST MA 224,250 299,000
EAST NY 224,250 299,000
West CA 266,250 355,000
West CO 226,250 355,000
But because we have [Region] on LoD the two rows for each group are actually printed on top of each other. You can see that clearly on sheet [Region on LoD] because the different numbers are over-printed. In the one with the totals, the over-printing would only show as a slightly darker font and Tableau would tell you you had 8 Marks on display (bottom left in status bar), as it does in [Region on LoD]. To avoid the overprinting and cut it down to 4 rows I've created another table calculation [First Row?] which just gives true for the first row in each [Group] allowing us to filter out the duplicates.
I hope that makes sense.
It's very hard to add anything on the performance question without seeing the exact structure, data distribution, indexing, etc, etc.
Thanks Richard. This was very helpful. I'll come say Hi in Vegas. Cheers!
Thanks Richard. That solved the issue for me. Much appreciated.
On a related note, is there a way to sort a field in the primary source by a field from the secondary source. For example:
- I have a chart that shows customer performance as measured by sales vs. target.
- Sales and customer name come from data source A.
- Target and customer name come from data source B.
- Data sources A and B are linked on customer name.
- I can calculate a "% attainment" measure in data source A that just compares sales vs. target.
- But when I want to sort the customer name field, the only fields that are presented in the 'Sort By' drop-down box come from data source A. What I want to do is sort customers by "% attainment".
Any way around this?