Hi Lee! The reason your blend breaks is that you're blending with Location as a linking field, so when you remove that from your view, Tableau can't blend any longer. In a blend, the secondary data source can only contribute aggregated values using the level of detail defined by the linking fields that appear in the view, so when you remove them, the blending calculation essentially becomes undefined.
Would you entertain the idea of giving users a parameter that lets them toggle between seeing the single-line total and the breakdown by location? If so, you could work around this issue.
Take a look at this attached demo:
The calculated field I added to both data sources (and then used as a linking field) is:
IIF([Show All], "All", [Location])
BlendExample.twbx 148.0 KB
Thanks David. This does work and I think I actually improved on it a bit. See what you think? All I did as add a Parameter (Locations) and then added a calc field to filter based on the selected Locations value (Location Filter). Then I set your Show All parameter to True.
By the way, I also realized I could just remove the CountD from the employee data source and got it to work. This was one of the problems I had when trying to do this.
But your method certainly would come in handy when I have cases when I have these types of aggregations.
BlendExample#3.twbx 145.5 KB
Even I was facing the same issue in Tableau 9.3 but only in few cases (its weird).
The connecting column in the blend was a date field.
Then I made a copy of the date field in both data sources and converted its data type to string and then connected it
It started working for me (again weird).
Thought of just sharing this point