Skip to main content

Creating Relationships Between Data Tables in Microsoft Power BI

Relate account, opportunity, and user tables in Power BI, then verify that account and owner filters return the expected opportunities.

Use relationships to analyze imported Yeeflow tables together. For example, filter opportunities by account industry, or show an account owner's name instead of an ID.

Start with Connecting Yeeflow with Microsoft Power BI and load the three example tables: tb_Accounts, tb_Opportunities, and tb_Users. The column names below belong to that example; check the corresponding fields in your own lists before creating relationships.


Step 1: Create Relationships:

First inspect the keys in Power Query. Both columns in each relationship should have the same data type. Confirm that account IDs are unique in tb_Accounts and user IDs are unique in tb_Users. The corresponding IDs can repeat in the tables that refer to them. Use IDs rather than names, which can repeat or change.

  1. In Power BI Desktop, open Modeling → Manage relationships → New. Review any relationships Power BI already detected before adding another.

  2. Select tb_Accounts and its Id column, then tb_Opportunities and its Account column.

  3. Set Cardinality to One to many (1:*) for that table order. One account can have several opportunities. Use Single cross-filter direction from accounts to opportunities, and select Make this relationship active. Select OK.

  4. Create the owner relationship: tb_Accounts[Account Owner] → tb_Users[AccountID]. In this example, AccountID in the users table identifies a user; verify that meaning against a known owner before using the field. Set Many to one (*:1) for this table order: several accounts can share one owner. Keep single-direction filtering from the unique user side to the accounts, and make the relationship active.

  5. Close Manage relationships and inspect the model diagram. Confirm the 1 and * ends, the filter arrows, and the active relationship lines.

Original Create relationship settings showing One to many (1:*) cardinality and Make this relationship active selected.

The crop above shows the account-to-opportunity settings from the original example. A solid relationship line is active; a dashed line is inactive. An inactive relationship does not propagate filters by default. Microsoft's active and inactive relationship guidance

If Power BI cannot use the intended one side, inspect duplicate keys and column types. Check blank or unmatched references against the source records rather than deleting valid rows merely to make the relationship save. Do not switch to Both just to make a visual change: bidirectional filtering can introduce ambiguous paths. Model relationship properties


Step 2: Visualize the Data:

Create a report page and test the relationships before adding more visuals.

  1. Add a table using account industry and an opportunity count or amount. Select an industry and compare the result with a small set of known accounts and opportunities in Yeeflow.

  2. Add the owner's name from tb_Users and the opportunity count or amount. Check one owner who has multiple accounts; the intended filter path is users → accounts → opportunities.

  3. Test an account with multiple opportunities, an account with none, and any opportunity whose account reference is missing. Investigate unexpected blank groups or repeated totals by checking the keys, active relationships, filter direction, and visual aggregation.

Once these checks pass, build the charts, tables, or other visuals needed for your report. Add interactivity or drill-down where it helps users explore the related data.


Keep a known-record check with the report so you can repeat it after a source-data or model change. Creating relationships does not repair missing references in the imported data.

Did this answer your question?