John, it's important to understand the architecture in play here.
When you create a new data source in Tableau, and apply a filter to it, you are creating a unique object for Tableau to interact with. This happens before ANY worksheets exist, and if you've filtered out certain rows, those rows do not exist in that data source as far as the workbook is concerned.
If you want a different set of data source filter rules applied (e.g. in the first data set, only keep 2018 data and in the second one only keep 2017 data) those are 2 completely separate things. They have no relationship to each other in the workbook, other than the fact that they both originated from the same root data source. Again, as far as Tableau is concerned they are unconnected and unrelated.
So, to get the terminology right, data source filters have nothing to do with WORKSHEETS. They are global to the WORKBOOK and exist before any worksheet content is created.
If you want 2 different data source filters applied, then you need 2 different DATA SOURCES. That's not the same as copying the existing data source once you're in the workbook - you have to create a new data source in the Data Source tab, and apply a different set of filter criteria there.
Does that all make sense?
It does make perfect sense. In my case, I expected a context filter to fulfill my need, but it just isn't working as expected (I expect it to "apply this "context" filter first, immediately after loading in the left joined data as per the data sources tab, and THEN apply all other filters"). My next attempt at getting this to work was to brute force it via a data source filter, which admittedly is overkill, but is getting the job done.
Problem is, this same filter impacts the dataset globally and therefore other worksheets are impacted.
I wish I could share the workbook but it is sensitive information.
I also wish that I could perform unique sql joins at the worksheet level. I could then remove the global left join which I have at the data sources level. This would be most efficient because I'd like to subset the source datasets PRIOR to joining (or add a where clause), and these subsets/wheres are expected to be different worksheet to worksheet.
Yeah, context filters can be confusing. Your definition is mostly right, but there are subtleties as you you have discovered.
There are almost certainly ways to get what you need but it's virtually impossible to debug without the data itself. My normal recommendation in these cases would be to start way back at the source data end of things. Is it possible to differentiate your data before you attach Tableau to it, and do some kind of union in Tableau? That might be a path to think about.
There are some changes coming to Tableau Desktop & Prep in 2018.3 that might be useful (relating to how the underlying tables are exposed in the .hyper extract file). But until we get something better than blends in the workbook (which are a pseudo-left join only) we're kind of stuck without getting creative on the data side.