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 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:
- Select File > Options and Settings > Options.
- In the Global section, scroll down and click on ‘Preview Features’.
- Ensure the ‘Relationship Pane’ is activated.
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:
- Right-click on the specific table (in our case, ‘internet sales’).
- Click ‘Manage Relationships’.
- 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.
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.
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.
ou 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.
Be the first to comment