Managing and editing table relationships typically refers to the process of defining and maintaining the connections between tables in a relational database. Table relationships are crucial for maintaining data integrity, enforcing referential integrity, and ensuring efficient data retrieval.
visualizationsPower BI is a powerful tool to understand and then manage and edit the table accordingly. It is used in data visualization which helps in drawing business intelligence reports from data. It is a tool by which data visualizations and dashboards can be created. But sometimes the data report/dashboard requires the analysis of two or more tables. Managing a single table in power bi is comparatively easy but when multiple tables are involved then things start getting complicated. So, this article will give you a brief about how to manage and edit multiple table relationships in Power BI.
Firstly, when more than one table is loaded, Power BI automatically detects the relationship between tables by looking at the column names. If Power BI can’t determine any potential relationship, it doesn’t create any. The table relationships can be viewed from the model view option present on the left side of the screen.
Model View
Autodetect Tool for creating relationships
If after loading all the tables, still no relationship can be seen between tables, then the Autodetect tool comes in handy. To use Autodetect, on the Modeling tab select Manage Relationships > Autodetect.
Autodetect
Manually Creating Relationships
But sometimes Power BI doesn’t create the correct relationships so there’s also an option of creating relationships between table manually. For this follow the steps below –
- On the Modeling tab, click Manage Relationships > New..
- Select the tables and columns that has a relation from the tables that were loaded. After select both tables and columns, certain options will be configured automatically such as Cardinality and Cross filter direction. It can be changed.
- Click OK.
But if none of the columns selected have unique values, an error will be displayed – One of the columns must have unique values, because this is a necessary condition to build a relationship between tables.
There’s also one other way that is to simple drag and drop the columns from one table to another in the model view diagram layouts.
Editing Relationships
For editing relationships between tables, Power BI provides two options which are using the edit relationships in the Properties Pane or using the Relationship Editor Dialog Box.
For the first method, Click any line from the model view diagram layout to view the relationship options in the Properties pane.
Properties Pane
Another way is using the Relationship Editor dialog box. Now to access the dialog box, follow any of the below steps –
- From the Modeling tab, click Manage Relationships. Select the relationship you want to edit from there and click the Edit button.
- Select any table from the Fields present on the right side of the screen, then select Table Tools > Manage Relationships and then select the relationship that needs to be edited > Edit.
Finally, Single or multi-selected relationships can also be deleted by pressing Delete on your keyboard. A dialog box will appear to confirm as this option can’t be reverted back.
Editing relationships using different methods
There are basically two methods of editing relationships in Power BI i.e. using the Edit relationships dialog Box and using the properties pane.
- Using the Edit Relationships dialog box is the most common way of editing relationships as it also provides a preview of the data. As columns are selected/changed, the cardinality and cross-filter direction automatically change.
- Using the properties pane is a more streamlined approach but there’s no preview of data. Only the table names and columns are shown. This is helpful if you want to create more advanced relationships between tables which is not possible by the dialog box.
Simple relationships can be made by simply dragging and dropping the column from one table to another.
Configure Options
When creating/editing relationships, you can configure many other options. By default, Power BI tries to make its best guess and automatically configures them but these options can be changed according to the user’s needs.
Cardinality
Cardinality basically defines what type of relationships the tables have between them.
Many-to-One: This is the default type of cardinality. Basically, this means that the column in a given table can have more than one instance of a value while the other table will only have one instance of the value.
Let’s understand this by an example. As you can see below, There are two tables – sales and customer information. The customer information will have unique values only but the sales can have multiple same values so the cardinality here becomes many-to-one.
many-to-one
One-to-One: This means that the column in one table has only one instance of value and the other table also has one instance of value.
One-to-Many: In one-to-many relationship, the column in the given table has one instance of value while as the other related table can have more than one instance of value.
As we reversed the tables below, the cardinality changes to one-to-many.
one-to-many
Many-to-Many: This type of relationship is when a column in both tables has duplicate values.
Cross filter direction
This option determines the direction of cross-filtering to be utilized for a two-column relation. It provides two types of options-
Single: This is the default type of direction in Power Bi, and it means that the filers will propagate in one direction only. This is mostly used with relationships having cardinality one-to-many and many-to-many.
Single arrowhead
Both: As the name suggests, the relationship will filter in both directions. It is commonly described as bi-directional. For one-to-one relationships, cross-filter direction is applied from both sides.
Bi-directional arrowhead
You can check the cross-filter direction between the tables by going to the Power BI desktop model view and noticing the arrowhead on the relationship line. If that arrowhead is single, it means the direction is single whereas if the arrowhead is double-sided, it means the relationship is a bidirectional relationship.
Automatic relationship updates
Power BI provides us with options by which you can specify how it handles the relationships. These options can be accessed from FIle > Options and Settingsare> Options. Then select Data Load on the left pane.
Automatic Relationship Update Options
- Import relationships from data sources on the first load: This option is selected by default. It basically checks for relationships that are already defined in the data source such as primary key/foreign key, etc., and then Power Bi replicates those relationships. This helps in saving time as you can directly start working on the data.
- Update or delete relationships when refreshing data: As the name suggests, Power Bi checks for any refreshed of the data made, then try to mirror those changes into its own data model, updating or deleting them to match.
- Autodetect new relationships after data are loaded: If this option is selected, the Autodetect feature of Power BI tries to make relationships between tables when the data is loaded.