Skip to main content

How to Create a Relationship Between Two Tables in Power BI

· 6 min read
Jagdish Kumawat
Founder @ Dewiride

A relationship tells Power BI how two tables match, so a filter on one reaches the other: pick a category, and the expenses of that category follow. Power BI sometimes adds relationships for you, and sometimes misses one. Here's how to create one yourself in the Power BI service.

Microsoft's Edit semantic models in the Power BI service describes two ways: drag a column onto a column of another table in the diagram, or use Manage relationships in the ribbon. The example uses the dialog. Its model, Expenses, has an Expenses table with a Category column, and an Expense Categories table with one row per category in Title (How to Create a Semantic Model Without a Report in Power BI). Power BI didn't relate them.

Step 1: Open Manage Relationships​

Open the model and select Manage relationships on the Home tab:

The model editor's Home tab with the groups Data, Queries, Calculations, Parameters, Security, Relationships, Model health and Explore, and the Manage relationships button outlined in red

If the ribbon's buttons are greyed out, switch the model to Editing first, as the article describes.

Step 2: Select New Relationship​

The Manage relationships dialog lists the model's relationships. In the example, it has the one Power BI made, Category managers (Title) to Expense Categories (Title). Select New relationship:

The Manage relationships dialog with the buttons New relationship, outlined in red, Autodetect, Edit, Delete and Filter, and one row: Category managers (Title), a many-to-one symbol, Expense Categories (Title), Active

Step 3: Pick the Tables and Columns​

In New relationship:

  1. From table: the table with many rows per value, here Expenses. A preview of its rows appears; select the column to match, Category.
  2. To table: the table with one row per value, here Expense Categories, and its column Title.

Power BI fills in the rest from the data:

SettingValueWhat it means
CardinalityMany to one (*:1)Many expenses can share one category
Cross-filter directionSingleA filter on Expense Categories filters Expenses, not the other way round
Make this relationship activeTickedPower BI uses it for filtering

The New relationship dialog with From table Expenses and its Category column selected in the preview, To table Expense Categories with Title selected, Cardinality Many to one (*:1), Cross-filter direction Single, Make this relationship active ticked, and the Save and Cancel buttons

Select Save.

Step 4: Check the Relationship​

The dialog lists two relationships now, both Active:

The Manage relationships list with Category managers (Title) to Expense Categories (Title) and the new Expenses (Category) to Expense Categories (Title), both many to one and Active

Close the dialog. In Model view, a line joins Expense Categories and Expenses, with 1 at the category end and * at the expenses end:

The model view with Expense Categories joined to Category managers, and a new line from Expense Categories, marked 1, to Expenses, marked *, with an arrow pointing towards Expenses

The arrow on the line shows which way filters flow: from the categories to the expenses.

Common Mistakes​

MistakeWhat happensDo this instead
Relating text names that can changeA renamed category breaks the matchRelate on values that don't change, such as IDs, when the data has them
Matching on a column whose values don't match exactlyRows find no partner and show as (Blank)Match on identical values
Leaving a wrong automatic relationshipVisuals filter by the wrong columnCheck the list, and edit or delete it

Troubleshooting​

SymptomLikely causeFix
The ribbon's buttons are greyThe model is in Viewing modeSwitch to Editing
Cardinality shows One to one (1:1)Each value appears once on both sides todayChoose the cardinality the data will have (Fixed: Apply Security Filter in Both Directions Is Missing in Power BI)
A visual shows a (Blank) groupSome values have no match in the other tableFix the values at the source

FAQ​

Can I drag instead?​

Yes: drag the Category column of Expenses onto the Title column of Expense Categories in the diagram, then check the settings that open.

What's Autodetect?​

The button next to New relationship asks Power BI to look for relationships itself, as it did when the model was created. Check whatever it adds.

When do I need Both?​

When a filter must flow from the many side back to the one side, such as for row-level security on a related table (How to Make a Relationship Filter in Both Directions in Power BI).

Conclusion​

Manage relationships > New relationship, pick the many side as From and the one side as To, check Many to one and Single, and save. The example joined Expenses (Category) to Expense Categories (Title).

Additional Resources​