Overview
Relationships in Power BI are essential for linking data between tables to create visualizations and models. Power BI will often automatically detect and create relationships between tables but you may need to manually connect. It also is very possible that you may not need to do this for a Power BI project, just depends on what you're trying to accomplish.
Navigation
In the Power BI Desktop app, navigate to the relationship icon on the left hand side:
This will then show a view of all tables:
Use Case
When creating Power BI projects, you will likely be working with multiple sets of data. A lot of this data will likely be from eMedley or Salesforce so there are many use cases in which you may need to link data between the two systems. An example of a use case could be that you need to pull the following all together:
- Salesforce - Contact: Student name, PID, OHIO ID, OHIO email, Primary Status, Secondary Status, Tertiary Status, Current Graduation Year
- eMedley - Schedule report: Student name, PID, email, Preceptor, Clinical Site, Rotation, Start Date, End Date, Clinical Education Cohort Site
In this instance you may need to primarily need data from the eMedley Schedule report but you want to tie in the Primary, Secondary, and Tertiary Status from the Salesforce contact record. You could use a data lookup measure to pull the Salesforce data into your eMedley data OR you can create a relationship to tie the data together.
When creating a relationship, you will need to have some kind of common identifier that exists between the two tables. In this instance the PID is likely the most unique and common data point in both tables.
Relationship Types
- 1:N represents a One to Many relationship - One Record to Many Records
- Examples:
- One Contact to Many Absence requests
- One Student to Many Rotations
- N:1 represents a Many to One relationship - Many Records to One Record
- Examples:
- Many Evaluations to One Contact
- Many Clinical Sites to One Affiliation Agreement
- N:N represents a Many to Many relationship - Many Records to Many Records
- Examples:
- Many Preceptors to Many Clinical Sites
Creating a Relationship
When you are adding new data sources, Power BI will often be able to autodetect a relationship based on the column headings and the data within it. If it does not autoconnect, here are the steps to creating a connection between one or more tables.
- An option is to drag the common field from one table to the other
- This will then bring up a dialog box.
- You'll notice a From and a To table that it will reference
- In this use case where I dragged PID from the 'SF Contact List' to the Student Number on the 'All Clinical Schedules', those columns are already highlighted and you will notice that the data is similar so this is a correct, unique field to use to link together.
- Below that, you will then be able to choose which direction (Cardinality) the relationship needs to be. It will often make a recommendation and many will fall into the Many to Many selection. Reference the Relationship Types above if needed. It may give an error but as long as it will let you save it should be fine. You can also update the Cross-filter direction, again this should default to what is detected but you can update if needed. There are likely additional explanations for these but this is a very high level overview of how it works.
Get help from HCOM IT
