- In this article, we learn about how to create and manage a relationship in Power BI.
- When we import multiple tables for analysis then the relationship between tables is necessary for the accurate calculation result.
- Relationship in Power BI is used to define the connections or the relation between two or more tables.
CREATE RELATIONSHIP
- Firstly we loaded tables into Power BI through the Get Data. Open Power BI Desktop → In Home tab click on Get Data → select SQL Server.
- In the SQL Server Database window, we provide Server and database name thereafter click on ok button.
- After that, we select the table and load it into Power BI Desktop.
AUTODETECT RELATIONSHIP
- When we load the tables then Power BI attempts to find and create a relationship for us.
- Power BI determines the column names in the table. If there are any potential relationships then only relationships are created.
- If Power BI Desktop can’t determine a match then it doesn’t create any relationship.
- However, we can use the Manage Relationships dialog box to manually create or edit relationships.
- By using the Manage relationship we can Add, Edit or Remove the relationship between tables.
- Here In the Manage Relationship Window, we don’t find any relationships.
- So we create a relationship manually. In the Manage Relationship close the Autodetect dialog box and click on the New button.
- After that Create Relationship window is open. Here we select the table in the drop-down list also we select related columns. Finally, click on the ok button.
- The relationship options Cardinality, Cross filter direction and Make this relationship active are automatically set.
ADDITIONAL OPTIONS
Cardinality:-
- Many To One:- In many to one relationship, The column in one table can have more than one instance of a value, and the other related table, often knows as the lookup table, has only one instance of a value.
- One To One:- In a one to one relationship, the column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value.
- One To Many:- In a one to many relationships, the column in one table has only one instance of a particular value, and the other related table can have more than one instance of a value.
- Many To Many:- We can establish a many-to-many relationship between composite tables, which removes requirements for unique values in tables.
Cross Filter Direction:-
- Both:- The Both setting works well with a single table that has many lookup tables that surround it.
- Single:- The most common and default direction, which means filtering choices in connected tables work on the table where values are being aggregated.
Make This Relationship Active:-
- It is a default relationship. When there is more than one relationship between two tables then this option is activated. It automatically creates visualizations that include both tables.
- Check the relationship Active checkbox and close the window.
- In the Power BI desktop, Click on the Model and it displays the relationship of the tables.
- Model is used to display the relationship between two tables in a diagrammatical form which is also called the Entity-Relationship (ER)Diagram.
If you are new to Power BI start with the following must-watch video:-
