Power BI: Creating Relationships Between Tables and Editing Using the New Relationship Pane

In this tutorial, Angelica Choo Quan, a trainer at Pragmatic Works, explains how to create and modify relationships in Power BI using the newly released relationship pane. This feature was introduced in the October Power BI update and offers users the ability to seamlessly manage relationships between tables in their data models.

Introduction to Relationships in Power BI

Power BI typically detects relationships automatically between tables in your data model. However, there are cases where Power BI may not detect these relationships correctly, requiring users to create or edit them manually. Understanding how to establish and modify these relationships is crucial for effective data analysis.

Activating the New Relationship Pane

To start using the new relationship pane, follow these steps:

• Navigate to File > Options and Settings > Options.
• Scroll down to the Preview Features section.
• Ensure the Relationship Pane is enabled.
• Click OK and restart Power BI for the changes to take effect.

Once enabled, you can easily access and manage relationships between your tables directly from the relationship pane.

Creating Relationships Between Tables

Angelica demonstrates the process using the AdventureWorks dataset. In this dataset, there are multiple tables such as Customer Information, Sales, Date, and more. To create a visual that shows total sales by date, follow these steps:

1. Select a table visual and add the Date and Sales Amount fields from the respective tables.
2. If Power BI fails to detect a relationship, go to the Manage Relationships option and open the relationship editor window.
3. Click New to create a relationship between the Date table and the Internet Sales table.
4. Select the appropriate columns that relate to each other (e.g., Date Key and Order Date Key).
5. Confirm the cardinality (typically many-to-one) and cross-filter direction.
6. Click OK to create the relationship.

Once the relationship is created, your visual will display the correct total sales for each date, fixing the issue of showing the same total sales amount for all dates.

Editing Existing Relationships

In some cases, you may need to modify an existing relationship. Here's how you can do that:

• Go to the Model View in Power BI.
• Hover over the relationship lines to see which columns are connected between tables.
• Select a relationship line and access the relationship pane to modify settings such as cardinality (one-to-one, one-to-many, etc.) and cross-filter direction (single or both directions).
• Apply changes to make sure your updates take effect.

Editing these relationships helps in refining your data model and improving the accuracy of your reports.

Power BI Cardinality and Cross-Filter Direction

Power BI automatically detects the cardinality of a relationship. Cardinality refers to the number of rows in each table that are related. The four cardinality types are:

• Many-to-One: One side has unique values, the other side has duplicate values.
• One-to-One: Both sides have unique values.
• Many-to-Many: Both sides may have duplicate values.

Cross-filter direction determines how filters apply to data in related tables. The options are:

• Single: Filters apply in only one direction.
• Both: Filters apply in both directions (bi-directional filtering).

Conclusion

Creating and managing relationships in Power BI is essential for building accurate and meaningful reports. With the new relationship pane, Power BI makes it easier to visualize and manage these relationships. By following the steps outlined in this tutorial, users can ensure that their data model is correctly configured, leading to more insightful analyses.

Stay tuned for more Power BI tutorials from Pragmatic Works, and make sure to visit our on-demand learning platform for additional resources on Power BI and other tools in the Microsoft Power Platform.

Video by Angelica Choo Quan, Pragmatic Works

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.