Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

Power BI Relationship: Guide to Create & Modify Data Connections

Power BI Relationships

In today’s post, I’ll walk you through the steps to create relationships in Power BI. We’ll also delve into editing existing relationships, leveraging the newly introduced relationship pane on  Power BI.

A Common Power BI Relationship Misconception

It’s a popular belief that Power BI identifies relationships automatically. And yes, most times, it does the job for you. However, on certain occasions, you might have to step in to either create new relationships or modify the existing ones. Grasping how these relationships operate and how one can modify them will prove invaluable.

Diving into Power BI Desktop

Before deep diving into the intricacies of relationships, it’s essential to activate the new relationship pane. To do this:

  1. Select File > Options and Settings > Options.
  2. In the Global section, scroll down and click on ‘Preview Features’.
  3. Ensure the ‘Relationship Pane’ is activated.

Creating and Modifying Relationships in Power BI

Remember, after turning on this feature, you’ll have to restart the Power BI Desktop.

 

For this guide, I’m using the AdventureWorks dataset by Microsoft. This dataset includes a variety of tables ranging from customer data to internet sales and product details.

The Core Task: Creating a Visual

Imagine wanting to produce a visual that displays total sales for each date. Now, while Power BI usually detects relationships between tables, sometimes, it might not. This calls for us to manually set up the relationship.

To manage relationships:

  1. Right-click on the specific table (in our case, ‘internet sales’).
  2. Click ‘Manage Relationships’.
  3. In the window that opens, you can view all the active relationships.

If you’re unsure about any missing relationships, there’s an option to autodetect. However, if that doesn’t help, you’d need to manually create one by clicking on the ‘New’ button. Then, define the tables and related columns.

For instance, with our data, the ‘date key’ from the date table and ‘order date key’ from the internet sales table seemed to have a logical connection. Establishing a relationship between these fields corrected the discrepancies in our initial visual.

Create relationship

Additional Tweaks

After setting the relationship, it’s always a good idea to fine-tune your visualizations. For instance, formatting the sales amount as currency gives a more professional look.

Editing Existing Relationships

Switching to the model view, one can observe the relationship lines between tables. Hovering over these lines provides insights into the key columns connecting the tables. And, if you wish to modify any relationship, simply select the respective line, and the relationship pane gives you an array of options, from changing cardinality to setting cross filter directions.

Editing existing relationship

Wrapping Up

That brings us to the end of this session on creating and adjusting relationships in Power BI. I genuinely hope this guide assists you in your Power BI journey.

 

You can visit the rest of our blog posts for more insightful information on everything related to Power BI.

Learn more about Power BI by taking our training course.

 

Leave a Comment

Your email address will not be published. Required fields are marked *