I don't think you can do what you are asking for in the current setup.
From what I have found you might need to join before they are sent into tableau. When you connect to data source choose multiple table, select both sources and define the join there. Once this is done you will have a single dataset that can be filtered.
I could easily Join all the data into one table for the simple example I gave, where I have population estimates at the level of county and ***. But ultimately I would have cases counts broken down to a level, like disease site, and the population estimates aren't broken down to that level. I would like to give the user the option to filter the calculated rates by county, ***, and disease site. So the rate would be calculated by the case count for that county, ***, and disease site divided by the population of the same county and *** that the user choose.
Is there another way I should be doing this? Can I create custom SQL with parameters set by the user and passed to the SQL?
I can't think of a way to do it through the SQL, but it might be possible to do it through a parameter in tableau. If you only wanted to filter on *** it would not be that difficult you would create a calculated field in each of the datasets that the parameter would control. It would act as a filter in the back ground. Where this can get difficult is when you want to filter by something with many options.