Handle Complex Relationships inside Power BI Desktop

Introduction

Modern self-service BI products like Power Pivot in Excel and Power BI Desktop are often used to mash up data from different sources. Once multiple tables are imported into the model, users create relationships among them so that they can easily slice and dice data using any column from related tables. For those users of Power BI Desktop who only create relationships in the Manage Relationships dialog box, Figure 1, or connect columns in the Diagram View, Figure 2, they often run into constraints which block them from creating the relationships they need. For example, users cannot create a relationship by joining more than one column from each table. In this blog post, I am going to show you a way to work around such constraints inside Power BI Desktop.

Figure 1.
Figure 1.
Figure 2.
Figure 2.

In Power BI Desktop, users can only create a relationship between a pair of tables by joining two columns, one from each table. Furthermore, at least one of the columns cannot contain any duplicate values or blank values; otherwise, you get errors seen in Figure 3 or Figure 4.

Figure 3.
Figure 3.
Figure 4.
Figure 4.

Users who are familiar with relational database technologies can recognize that relationships are modeled after foreign key to primary key constraints. In relational databases a primary key column uniquely identifies each record in the table, must contain unique values, and cannot contain NULL values.

If you have already imported tables into the model but cannot create relationships due to the above requirements, you are typically told to work around the constraints by

  • creating a calculated column to combine multiple join columns into a single one so you can create relationships between two calculated columns;
  • introducing a third table that contains unique and non-blank values of the join columns and then creating two relationships between each of the original tables and the third table so that the original tables are related indirectly.

The introduction of bi-directional cross-filtering and calculated tables in Power BI Desktop has made both techniques easier to implement than in Power Pivot. Today I want to show you another way of working around the constraints: sometimes you can join two tables inside Query Editor and then bring a single table with all the columns you need into the model.

The test data for the two examples I am going to show can be downloaded from here. You can also download the final pbix file after we have completed the two examples from here.

First Example

In our first example, we want to create a relationship between the two [ProjectID] columns as shown in Figure 5. [ProjectID] is unique in Table1 except sometimes it is blank when a program has been created without any project yet.

Figure 5.
Figure 5.

Assume we want to perform some cost analysis on each program. Although we cannot create a relationship between Table1 and Table2 due to the blank value, it’s sufficient if we can bring [ProgramID] into Table2. Here is how to achieve this in Power BI Desktop.

Once you have selected Table1 and Table2 in the Navigator, click the Edit button, as shown in Figure 6.

Figure 6.
Figure 6.

Select Table1 and then click the Properties button in the ribbon as shown in Figure 7.

Figure 7.
Figure 7.

Uncheck “Enable load to report” and then click OK, see Figure 8. We bring in Table1 just so we can merge its [ProgramID] column into Table2, afterwards, it has served its purpose therefore no longer needed when we build reports.

UncheckEnableLoadToReportTable1
Figure 8.

Select Table2 in Query Editor and then click the Merge Queries in the ribbon, see Figure 9.

MergeQueries
Figure 9.

In the Merge dialog box, click [ProjectID] under Table2, select Table1 in the dropdown box, click [ProjectID] under Table1, select “Full Outer (all rows from both)” as Join Kind, and then click OK. We have chosen “Full Outer” just in case we want to build reports including programs without any projects yet.

Figure 10.
Figure 10.

A new column has been added to Table2 where each row shows Table. The previous step only makes columns of Table1 available in Table2, but we still have to actually pick the individual columns we care about. Click the button at the upper right corner as shown in Figure 11.

Figure 11.
Figure 11.

Since we just want to bring [ProgramID] into Table2 with the same name, keep only ProgramID checked, uncheck the other two boxes and then click OK, see Figure 12.

Figure 12.
Figure 12.

Table2 now has the [ProgramID] column, we can close Query Editor, shown in Figure 13, and start building reports.

Figure 13.
Figure 13.

Second Example

As a second example, we examine Table3 and Table4 as shown in Figure 14. We want do some analysis on Table3 but we also need the [Principal Amount] column from Table4.

Figure 14.
Figure 14.

We cannot create a relationship directly since we must join by both the [Entity Code] column and the [Fiscal Month] column. Although we can create a calculated column on each table to combine [Entity Code] and [Fiscal Month], it’s an artificial and strange thing to do. Instead we will leverage Query Editor’s flexible ability to merge two tables. The steps are identical to those in the first example except that we pick two columns from each table in the Merge dialog box. You can select multiple columns from a single table by holding the Ctrl button and then clicking the columns you want. Note that the order of clicking is important as it determines which pairs of columns will be matched during the merge. The UI conveniently labels the columns with numbers in the order you click, as shown in Figure 15.

Figure 15.
Figure 15.

Conclusion

Today I described a way to work around limitations of relationships by merging tables in the Query Editor before importing them into a model. Strictly speaking, this technique is not the same as creating relationships but, if used correctly, often gives us what we need. Users who are familiar with Power Query Formulas know that they can join tables in more flexible and powerful ways than allowed by the UI. By packaging two powerful engines, one from Power Query and one from Power Pivot, into a single product, Power BI Desktop gives users maximum versatility to shape their data any way they want.

Advertisements
Handle Complex Relationships inside Power BI Desktop

4 thoughts on “Handle Complex Relationships inside Power BI Desktop

  1. Siva Prasad says:

    the problem is that when we merge two tables(say LEFT), it never gives the correct count. in this case, left table(1000) records and lookup table(100), ideally it should give 1000 records but it wont give correct count here. any workaround for that?

    Like

  2. djpirra says:

    This would actually be interesting if it would work in direct query mode… also, direct query mode to relational databases should support join with multiple fields…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s