Welcome to Excel Jet Consult blog post. In this tutorial, we want to take a cursory look at one-to-many relationships in Power BI.
Creating relationships across imported multiple tables is important to perform accurate and reliable and easy-to-understand analysis for sound decision making. By default, Power BI is equipped to perform automatic relationship detection and creation between fact and dimension tables based on the same column names. The relationship cardinality, cross filter direction and active properties are automatically handled by Power BI Desktop behind the scene.
FACT & DIMENSION TABLES
Fact table is a table that consists of business transaction and foreign keys that refers to primary keys in the dimension table. In other others, fact table contains repeated business transaction records that occurs over many periods of time. It is possible to have more than one fact table unlike what we have in this example.
Dimension table consists of unique transaction records and primary keys based on the repeated transaction records in the fact table.
In the caption below, we have Two Fact Tables (Import_Data and Export_Data) and Three Dimension Tables (Dim_SubRegion_Countries, Dim_Month_Sort and Dim_Region). One good practice in data modelling is that dimension tables should have Dim or unique prefix which will make it easier for “greenhorns” in data modelling to easily understand which tables are dimension and fact tables respectively
After performing cleaning and transformation in the Power Query editor, I loaded the queries into the data model. Automatically, Power BI detected exact column names between the fact tables and the dimension tables and created the one-to-many relationships. For instance, in the Export_Data and Import_Data tables, we have Region tables that correspond to the Region table present in the Dim_Region. Hence, we can see the asterisk (*) sign from both Export_Data and Import_Data connected to the Dim_Region table which has double 1 staked above each other.
Furthermore, Power BI detected and created one-to-many relationship based on the Month column name in both the Export_Data, Import_Data and the Dim_Month_Sort. Same with Sub-Region/Country across fact and dimension tables.
One of the things we need to understand is that when we hover the mouse to the relationship connecting lines, we can point to the column names that each relationship is established on. When we right-click on the lines, we have two options to choose from:
- Delete the relationship
- Properties (Edit Relationship)
In the caption below, I clicked on Properties and I have the Edit Relationship dialogue box opened. In the dialogue box, we can see the Table and Columns in which the relationship was created. Also, we can see the Cardinality (Many to One) and Cross Filter Direction (Single). The Relationship is Active
POWER BI UNABLE TO AUTO-DETECT RELATIONSHIP?
In a situation whereby Power BI do not auto-detect and create one-to-many relationships across the fact and dimensions tables, that implies that the column name is slightly or wholly different from across the tables. For instance, assuming we have Region column in the fact table and we have Regions in the dimension table, Power BI will not auto-detect and create one-to-many relationship between Region and Regions because they are not the same. To buttress this point, I renamed the Import_Data Region to Regions in the source data and loaded the queries to the data model and of course, Power BI evaluates the queries and perform auto-detection to create relationship.
However, in the caption below, we discovered that, no relationship was created between the Import_Data and Dim_Region tables because we have Regions in the Import_Data and Region in the Dim_Region
In the above situation, it is required to manually create the relationship by dragging Region and connect it to the Regions in the other table. We can create the manual relationships by following the steps below:
- In the far-right of the Home tab, click on Manager Relationship
- In the Manager Relationship dialogue box, click on New (bottom left)
- In the Create Relationship dialogue box, select the Import_Data table name from the drop-down
- Select Regions column
- Select Dim_Region table name from the second drop-down
- Select Region column
- Click OK and another OK.
From the caption below, we have the relationship created on one-to-many cardinality
CONCLUSION
It is #Pivot-al to understand our data and cross-check to ensure that column names match one another across corresponding tables for seamless data analysis in Power BI