Framing Data Modelling in Power BI – How to create relationship, Cardinality, Cross filter direction.
High level resolution steps
- I have to create relationship between “Employees” and “EmployeeSkills” table.
Detailed resolution steps for Data Modelling in Power BI
Step 1: After clicking on Model View your Power BI Desktop looks like this.
Step 2:
- It will show the model representation of your tables.
- Now to create relationships in the Home menu bar select Manage Relationships.
- After clicking on Manage Relationships, Manage Relationships prompt display.
Note: – Sometimes it will AutoDetect the relationship and create relationships.
Step 3:
- Now to create relationship click on New option after clicking on new you will get create relationship prompt window.
- In Create Relationship prompt select tables and columns that are related.
Step 4:
- If you see the above image, I selected “Employees” and “EmployeeSkills” table and
both the table connected with “EmployeeID”. - After selecting the required tables or columns click OK and then close the Manage
relationship window. - Now in the model view you’ll see some changes that means your relationship created like shown in the below image.
Cardinality:
Power BI cardinality is defined by the relationship, and it refers to the relationship between two tables.
There are four types of cardinalities in Power BI: –
- One-to-one
- One-to-Many
- Many-to-One
- Many-to-Many
In Cardinality we’ll get options like the image below.
When we create relationships, it is recommended that the joining field contains unique values in at least one of the tables. This allows you to use the one-to-many or many-to-one options in your data model.
Cross Filter Direction: –
Cross filter direction gives a choice between either single or both directions. Relationships flow from the table with unique values to the table with many values.
Saumya Anilkumar
