Create Table Relationships
Learn how to create, configure, test, and troubleshoot table relationships in Power BI. Relationships connect related tables so that filters and calculations can work across the data model.
1. What Is a Table Relationship?
A table relationship connects two tables through columns containing corresponding values. For example, a Sales table may contain ProductID values that correspond to ProductID values in a Products table.
When the relationship is configured correctly, a user can select a product category in a report and analyze the sales associated with that category.
Why Relationships Are Useful
- Connect transactional data with descriptive information.
- Allow dimension fields to filter related fact records.
- Support calculations across multiple tables.
- Reduce the need to repeat descriptive columns in every table.
- Help organize a model for interactive reporting.
Visual 1: A one-to-many relationship between Products and Sales.
2. Prepare Tables Before Creating Relationships
Before connecting tables, verify that the matching columns contain compatible values and data types. A relationship may fail or behave unexpectedly when keys contain duplicates, nulls, inconsistent formats, or incompatible data types.
| Check | What to Verify | Example |
|---|---|---|
| Matching columns | Both tables contain corresponding key fields. | Products[ProductID] and Sales[ProductID] |
| Data types | Key columns use compatible data types. | Both ProductID columns use text. |
| Unique dimension keys | The one-side column contains unique values. | Each ProductID appears once in Products. |
| Missing values | Required keys are not unexpectedly blank. | Sales rows have valid ProductID values. |
| Matching records | Fact-table keys correspond to expected dimension records. | Sales ProductID values exist in Products. |
3. Create a Relationship in Model View
Model view displays tables and their relationships visually. You can create a relationship by dragging a column from one table to its matching column in another table or by using the Manage relationships dialog.
Method A: Drag and Drop
- Open Power BI Desktop.
- Load the Products and Sales tables.
- Select Model view from the left navigation.
- Locate ProductID in the Products table.
- Drag Products[ProductID] onto Sales[ProductID].
- Review the relationship settings in the dialog.
- Confirm the cardinality and cross-filter direction.
- Select OK to create the relationship.
Method B: Manage Relationships
- Open the Modeling tab.
- Select Manage relationships.
- Select New.
- Choose the first table and its key column.
- Choose the second table and the matching key column.
- Set the appropriate cardinality and filter direction.
- Confirm the settings and create the relationship.
Visual 2: The basic process for creating and testing a relationship.
4. Configure Relationship Properties
Power BI relationship settings control how tables connect and how filters move through the model. The correct settings depend on the uniqueness of the keys and the intended analysis.
| Property | Purpose | Typical Choice |
|---|---|---|
| Table and column | Identifies the columns being connected. | Products[ProductID] and Sales[ProductID] |
| Cardinality | Defines how rows correspond between tables. | One-to-many for Products to Sales |
| Cross-filter direction | Defines how filters propagate through the relationship. | Single direction for many common dimension-to-fact models |
| Active status | Determines whether the relationship is used by default. | Active when it is the intended default filter path |
Active and Inactive Relationships
An active relationship is used by default when filter context travels through the model. An inactive relationship is retained in the model but does not normally propagate filters unless a supported DAX expression activates it for a calculation.
Inactive relationships can be useful when two tables have multiple valid relationships, such as an Orders table containing both OrderDate and ShipDate.
Visual 3: A model may use an active OrderDate relationship and an inactive ShipDate relationship.
5. Test Whether the Relationship Works
After creating a relationship, test it using a simple report visual. A relationship can exist in the model but still produce unexpected results if the keys, cardinality, or filter direction are incorrect.
Testing Procedure
- Open Report view.
- Add ProductName from the Products table to a table visual.
- Add a Total Sales measure using the Sales table.
- Check whether the sales values appear for the expected products.
- Add Category as a slicer or filter.
- Change the category selection and inspect the results.
- Investigate blank or unexpected totals.
Visual 4: Use a table visual to check whether product filters affect sales correctly.
6. Troubleshoot Common Relationship Problems
| Problem | Possible Cause | What to Check |
|---|---|---|
| Relationship cannot be created | Duplicate values on the one side or incompatible key columns. | Check uniqueness and data types. |
| Blank category appears in a visual | Some fact-table keys may not match dimension keys. | Inspect unmatched keys and missing dimension records. |
| Filters do not affect a measure | Relationship may be inactive or the filter path may be incorrect. | Review active status and cross-filter direction. |
| Totals appear too high | Duplicate records, incorrect granularity, or unsuitable relationship design. | Check table grain, keys, and aggregation logic. |
| Ambiguous relationships | Multiple paths may connect tables. | Review relationship paths and simplify the model where appropriate. |
7. Practical Exercise
Exercise: Connect Products and Sales
Use the following tables in Power BI Desktop:
- Products: ProductID, ProductName, Category.
- Sales: OrderID, ProductID, Quantity, SalesAmount.
- Load both tables into Power BI.
- Verify that Products[ProductID] contains unique values.
- Check that both ProductID columns use compatible data types.
- Create a one-to-many relationship from Products to Sales.
- Review the cross-filter direction and active status.
- Create a Total Sales measure.
- Build a table visual containing ProductName and Total Sales.
- Add Category to a slicer and test the results.
- Investigate any unmatched product identifiers.
- Document the relationship settings and validation results.
Expected outcome: A working relationship that allows product fields to filter sales calculations.
8. Knowledge Check
Question 1: What is a table relationship?
A. A visual formatting option
B. A connection between matching columns in related tables
C. A report title
D. A type of chart
Question 2: Which table commonly contains the unique ProductID values?
A. Products dimension table
B. Sales transaction table only
C. Report page table
D. Visual formatting table
Question 3: What should you check before creating a relationship?
A. Only the report theme
B. Only the page size
C. Key values, uniqueness, and compatible data types
D. Only the chart title
Question 4: What is an active relationship?
A. A relationship used by default for filter propagation
B. A relationship that deletes records
C. A hidden report page
D. A relationship that changes column names
Question 5: What should you do if product filters do not affect sales?
A. Delete every table
B. Check the relationship, filter path, and measure
C. Change all keys to random numbers
D. Remove all measures
View Answers
- B — A connection between matching columns in related tables.
- A — The Products dimension table.
- C — Key values, uniqueness, and compatible data types.
- A — A relationship used by default for filter propagation.
- B — Check the relationship, filter path, and measure.
Summary
In this lesson, you learned how to prepare matching key columns, create relationships through Model view or Manage relationships, and configure relationship properties such as cardinality, cross-filter direction, and active status.
You also explored how to test relationships with report visuals and troubleshoot common issues. Always validate key columns and confirm that filters produce the expected results before relying on the model for analysis.