How to Create a Relationship in Tableau

Connecting different data tables is fundamental to analysis, but for a long time, the process was rigid and unforgiving. Tableau Relationships changed the game by offering a smarter, more flexible way to combine your data without the headaches of traditional joins. This guide will walk you through exactly how to create and use relationships in Tableau, explaining what they are and why they are now the default method for data modeling.

What Are Tableau Relationships? (And How Are They Different from Joins?)

Before jumping into the “how,” it’s useful to understand the “why.” If you’ve worked with data before, you’re probably familiar with the concept of joins (inner, left, right, outer). Joins physically merge tables into a single, often very wide, flat table based on a specific rule you define before you start your analysis. This process is permanent for that data source and can lead to issues like data duplication or lost records if you choose the wrong join type.

Relationships, introduced in Tableau 2020.2, are a different approach. Think of them as a flexible contract between two tables. Instead of merging them into one big table immediately, relationships keep the tables separate and distinct. When you build a visualization, Tableau looks at the fields you're using from each table and decides on the fly what kind of join is needed in that specific context. This approach is smarter and avoids many common problems.

Here’s a simple breakdown of the key differences:

• Joins: You combine tables upfront into a single, fixed table. The structure does not change, regardless of what you are analyzing.
• Relationships: You define a connection between tables, but they remain separate. Tableau intelligently joins them behind the scenes based on the specific worksheet you’re building.

In short, the default mantra in Tableau is: Use relationships whenever possible. Only revert to joins when you have a specific and intentional reason for permanently flattening your data.

Step-by-Step Guide: How to Create a Relationship in Tableau

Let's walk through creating a relationship using a common business scenario. Imagine you have two tables in an Excel file: one with order information (Order ID, Order Date, Sales) and another with information about returns (Order ID, Returned Status).

Step 1: Connect to Your Data

First, open Tableau and connect to your data source. This could be an Excel file, a Google Sheet, a database like SQL Server, or any other supported connector.

For this example, we’ll connect to a single Excel file that contains two sheets: ‘Orders’ and ‘Returns’.

1. On the start page, under Connect, click on Microsoft Excel.
2. Navigate to your file and click Open.
3. You'll be taken to the Data Source page. You should see your sheets listed on the left panel.

Step 2: Drag Your First Table to the Canvas

The empty area that says "Drag tables here" is your canvas for data modeling.

1. Click and drag your primary table, Orders, onto the canvas.
2. Tableau will display a preview of the data in the grid below. This is your "logical table."

Step 3: Add a Second Table and Create the Relationship

Now, let's connect the 'Returns' data.

1. Drag the Returns sheet onto the canvas. As you get close to the Orders table, an orange line will appear. This is what Tableau calls "the noodle."
2. Release the mouse button. The noodle will connect the two tables, and the Edit Relationship dialog box will pop up automatically.

Tableau is smart enough to inspect the fields in both tables and often finds the matching column on its own. In this case, it correctly identifies that both tables share an ‘Order ID’ field and uses that to establish the relationship.

Step 4: Configure the Relationship (Performance Options)

The "Edit Relationship" dialog is where you can fine-tune the connection. While Tableau’s defaults are usually excellent, understanding these options will give you more control.

You’ll see two main customization sections under Performance Options: Cardinality and Referential Integrity.

Cardinality

Cardinality tells Tableau how records in the two tables correspond to each other. Your options are:

• One to One (1:1): Each record in the first table relates to exactly one record in the second table. (Example: A table of employees and a table of their assigned company laptops).
• One to Many (1:N): Each record in the first table can relate to multiple records in the second. (Example: A table of customers and a table of their orders). This is very common.
• Many to One (N:1): Multiple records in the first table can relate to a single record in the second. (It's the reverse of the one-to-many relationship).
• Many to Many (N:N): Records in both tables can relate to multiple records in the other. (Example: A table of students and a table of classes).

Tableau defaults to 'Many to Many', which is the most flexible option but might not always be the most performant. For our Orders and Returns example, each order can appear only once in the Returns table, but an order will appear multiple times in the Orders table if it contains multiple products. So, a 'Many-to-One' relationship (from Orders to Returns) is more accurate.

Referential Integrity

This tells Tableau whether all matching records are guaranteed to exist in the other table.

• Some Records Match: This is the default and safest setting. It tells Tableau that not every record in one table will have a match in the other. For example, not all orders are returned, so many orders won't have a match in the 'Returns' table.
• All Records Match: You should only select this if you are certain that every record in the first table has a corresponding match in the second. Telling Tableau this can improve query performance because it doesn’t have to check for null values.

For most scenarios, including this one, 'Some Records Match' is the correct choice. Once you’re happy with the configuration, close the dialog box.

Step 5: Start Building Visualizations

Now for the best part. Go to a new worksheet. On the left side, you'll see your data is organized by table: Orders and Returns. The fields are not mashed together.

Let's build a simple view to see total sales for returned vs. non-returned orders:

1. From the Orders table, drag the Sales measure onto Rows.
2. From the Returns table, drag the Returned dimension onto Columns.

Instantly, Tableau generates a bar chart showing two bars: one for sales of orders marked as 'No' for return, and one for those marked 'Yes'.

Look how easy that was! You didn't have to specify "use a left join." Tableau just understood the context. It looked at the fields you used and performed the correct aggregation, connecting the data via 'Order ID' in the background to give you the exact view you asked for.

Common Challenges and Tips

While relationships are mostly intuitive, here are a few tips for handling common situations.

Mismatched Field Names

What if your ID field is named 'Order ID' in one table and 'OrderID' (without space) in another? Tableau might not automatically match them. No problem.

In the "Edit Relationship" dialog, you can manually click on the field names for each table and select the correct matching columns from the dropdown lists. Easy fix.

Creating Relationships on Multiple Fields

Sometimes a relationship needs more than one field for a unique match. For example, you may need to match on both 'Region' and 'Product ID'.

In the "Edit Relationship" window, simply click "Add more fields" to define a multi-part relationship key. Tableau will then only consider records as matched when all the selected fields align.

When should you still use joins?

Relationships are the default, but physical joins aren't obsolete. You "double-click" a logical table in the data canvas to open the "physical layer," where you can define traditional joins.

You might do this for specific reasons, such as:

• Row-level security: If you need to filter the data source to a specific set of rows permanently for security reasons.
• Data transformation: When you need to do specific calculations or transformations that rely on the data being in a specific, flattened structure before any analysis occurs.
• Performance on well-known schemas: In some rare cases with highly predictable data structures, manually defining a join might be slightly more performant than letting Tableau manage the relationship. However, you should always start with relationships and only optimize to joins if you've identified a performance bottleneck.

Final Thoughts

Tableau Relationships revolutionized data modeling within the tool, making it far more intuitive and forgiving. By keeping your tables separate and handling joins contextually on a per-viz basis, you retain the full fidelity of your data, avoid common duplication errors, and get to your insights faster.

Ultimately, the goal of any data tool is to shorten the distance between data and decision-making. Tools like Tableau are incredibly powerful, but even their simplified features require a bit of setup and a learning curve. We created Graphed to take this simplification to the next level. Instead of connecting tables and dragging fields, you just connect your data platforms and ask questions in plain English, and a real-time dashboard is built for you in seconds, no configuration needed.