You might want to do some research with data from several Power BI tables when you load them. You’ll need relationships between these tables to correctly compute results and present relevant information in your reports. The auto-detect function of Power BI Desktop makes it easy to create these connections.
It is important to be able to change and establish relationships in Power BI Desktop to form new partnerships or adjust to existing ones.
Data Relationships in PowerBI
Power BI allows you to work with data structures that contain data from multiplePower BI tables. These data structures must be linked via relationships. For visuals to be sliced by data from different tables, connections are required.
After you have connected the tables with a relationship, you can work with both tables as if they were one table. This will allow you to not have to worry about relationships or combining columns and row into one table.
If Power BI Desktop is unable to detect a connection between two Power BI tables, then no automated relationship building can occur. In such cases, you will need to establish a connection.
How does Power BI automatically establish relationships when loading is complete?
Power BI Desktop attempts to establish relationships between data after you have entered it. Power BI Desktop examines the column names of the Power BI tables that you are querying to determine if there are potential correlations. If there are any connections, they will be automatically made.
If Power BI Desktop is unable to determine if there is a match with a high degree of confidence, the relationship will not be created. You can still manually create or modify relationships using the Manage Relationships dialogue. Click Manage Relationships > Autodetect in the Modeling tab.
Manually creating multi-table relationships
Select Manage relationships > New from Modeling menu.
Select a table from drop-down menu in the Create relationship dialogue box. Select the column that you want to use in the connection using the drop-down menu.
Select the second table from the drop-down menu. Click OK to select the column you would like to use.
Power BI Desktop configures the parameters for your new connection, including Cross-filter direction and Cardinality (direction), and makes it active by default. These settings can be modified if necessary.
Power BI allows you to edit a relationship
It is important to update your connections after each column from a table relates to your tables. This can be done using:
Power BI Desktop automatically configures the Cross filter direction, Cardinality, and Active attributes when you are creating relationships. These settings can be changed and should always been confirmed.
You can choose from the following options to use the Cardinality option
Many to one (*:1): This is the most common and default connection. This means that a column within one Power BI table might contain multiple value instances. The other Power BI table, also known as the lookup table has only one value occurrence.
One to one (1:1) – In a one to one connection, each column in a Power BI table has only one occurrence of a particular value, and each connected table only one instance.
One to many (1 :*): In a connection of one-to-many, a column in one Power BI Table has only one instance of a given value. The other table may contain multiple instances of the same value.
Many to many (*:*): It is possible to create many-to-many relationships between tables using composite models. This eliminates the need for each table to have unique values. To create associations, you can add new tables.
The Cross-filter direction option has the following options:
Both: Both tables can be used for filtering. Both options work well when there is only one table and many lookup tables around it. This arrangement is also known by the star schema arrangement. The Both option is used to combine a sales actuals table and a department lookup list. If you have multiple tables with lookup tables, you shouldn’t use the both option.
Single: This option is for data that are aggregated in the default, most common direction. All relationships will be preserved and all relationships will use the same approach when you import a Power Pivot model into Excel 2013 or later.
Getting Microsoft Certified: Power BI Fundamentals
Microsoft Power BI, a self-service software that allows you to create reports and dashboards, is a self service application.