I am working with two separate data sources where the join keys don't match exactly. For example, one table has a "Full Name" while the other has "First Name" and "Last Name" in separate columns. I need to join them by concatenating the names, but Tableau doesn't seem to let me select a calculation as a join key in the standard relationship canvas. Is there a way to create a "Join Calculation" on the fly, or do I need to prepare the data in a tool like Tableau Prep or SQL before bringing it into Desktop?
3 answers
In modern versions of Tableau, you can create "Join Calculations" directly within the Data Source tab. When you drag your tables into the canvas to create a join or relationship, click on the field selection dropdown. At the very bottom of the list of fields, you will see an option labeled "Create Join Calculation...". This opens a calculation editor where you can write functions like [First Name] + " " + [Last Name] or UPPER([ID]). This allows you to transform your data to match the keys in your second table without needing to modify your underlying database or use a separate prep tool.
Have you considered using Relationships (the "Noodle") instead of physical joins? Relationships are often more flexible because they don't flatten the data into a single table, which helps prevent duplication if you have different levels of detail. You can use the same "Create Relationship Calculation" logic there. However, be aware that complex calculations in joins can sometimes impact performance on very large datasets, as the calculation must be evaluated for every row during the query execution.
If you are using an older version of Tableau that doesn't support join calculations, your only other option within the app is Data Blending. You create the calculation in each data source separately and then link them in the worksheet. But honestly, Join Calculations are much cleaner if you're on a version from the last few years.
I agree with Jessica. Data Blending should be a last resort because it has so many limitations with LOD expressions and distinct counts. The "Create Join Calculation" feature really changed the game for handling messy data on the fly!
Bradley makes a great point about performance. If you find the dashboard is getting sluggish, that's usually the sign that the join calculation is too heavy. In those cases, I usually move the logic "upstream." If it's a simple concatenation, Tableau handles it fine, but if you're doing complex Regex or string parsing during the join, it’s much better to do that in a SQL view or Tableau Prep first so the index is already optimized when it hits Desktop.