Here are the steps I typically use in a case like this:
- Re-create your data connections - use the same fields and field names as original connection **Do NOT re-create any calculated fields
- Connect your new data source in addition to the old data source
- Look over your source fields (without = in front) to ensure you haven't re-named any of the fields in your prior version. If you have aliased fields, change the name in your new data connection as well.
- Highlight any calculated dimensions (starting with =), then right-click and select copy
- Switch your view to your new data source (just click on the name)
- Right-click in the dimensions window and select paste. Your calculated dimensions should now show up with no (1) afterward and no exclamation points.
- Repeat the above 3 steps with any calculated measures.
- To preserve any formatting based on your data source (colors, etc.), click through and let each dashboard render.
- Now you can right-click on your original data source and select "Replace Data Source", then select your new data source.
- Click through your dashboards - they should all be fine now.
A lot of steps and a complicated process, I know. It would be great if it were easier and/or more intuitive. Sometimes it is easy, but other times you have to be careful to handle all of Tableau's little picky things as you go. If you do it enough times it becomes second nature and much faster.
Thanks so much for the well thought out and informative post!
You're very welcome! Took me years (really), to get that whole process down pat. Ugh.
P.S. Just found out from a reliable source that you do not, in fact, have to click through every dashboard in step 8. Instead, switch to thumbnail view, right-click and "Refresh All Thumbnails". This stores the formatting metadata in Tableau's memory banks so that it's available when you make the switch. Otherwise it will revert to defaults.