Building relationships

One could argue that the building of relationships is the most important piece of Power BI Desktop. It is this process, the building of relationships, that makes everything else work like magic in Power BI. The automatic filtering of visuals and reports, the ease in which you can author DAX measures, and the ability to quickly connect disparate data sources are all made possible through properly built relationships in the data model.

Sometimes, Power BI Desktop will create the relationships for you automatically. It is important to verify these auto-detected relationships to ensure accuracy. 

There are a few characteristics of relationships that you should be aware of, and that will be discussed in this section:

• Auto-detected relationships
• There may be only one active relationship between two tables
• There may be an unlimited number of in-active relationships between two tables
• Relationships may only be built on a single column, not multiple columns
• Relationships automatically filter from the one side of the relationship to the many side
• Relationships cannot be built directly between tables that have a many-to-many relationship

Open up the .pbix file Chapter 3 - Building the Data Model.pbix found in your class files.

Let's take a closer look at each of the four items highlighted in the preceding screenshot:

1. Relationship: The line between two tables represents that a relationship exists
2. Direction: The arrow indicates which direction that filtering will occur
3. One side: The 1 indicates the Customer table as the one side of the relationship
4. Many side: The * indicates that the FactInternetSales table is the many side of the relationship

The first thing you should do after importing data is to verify that all auto-detected relationships have been created correctly. From the modeling ribbon, select Manage Relationships:

This will open up the Manage Relationships editor. The relationship editor is where you will go to create new relationships and edit or delete existing relationships. In this demo, the relationship editor will be used to verify the relationships that were automatically created by Power BI Desktop.

Let's take a look at the Manage Relationships editor, in which you can manage or perform the following:

• Current relationships in the data model
• Create a new relationship
• Edit existing relationships
• Delete a relationship

First, you need to verify auto-detected relationships. The top half of the relationship editor gives you a quick and easy way to see what tables have relationships between them, what columns the relationships have been created on, and if the relationship is an active relationship. We will discuss active and inactive relationships later in this chapter:

Take a look at Figure 4, You will see that there are currently three relationships, and all three relationships are currently active. The first relationship is the relationship between the CustomerKey column in the FactInternetSales table and the CustomerKey column in the DimCustomer table. This relationship was created automatically by Power BI Desktop when the tables were imported into the data model, and this is a valid relationship. In fact, all three relationships are valid.