Connect any number of tables together via a common column

One of the key powers of Power BI is the ability to bring data from different sources into a single model and then join them together by creating relationships among tables to perform analysis and create reports across all the data in the unified model. But a lot of people are confused when they run into one of the errors below while trying to create a relationship between two tables inside Power BI Desktop:

• We cannot create a relationship between ‘Table A'[Column X] and ‘Table B'[Column Y]. This could be because there is missing intermediate data to connect the two columns.
• You can’t create a relationship between these two columns because one of the columns must have unique values.
• You can’t create a relationship between these two columns because the ‘Column X’ column in the ‘Table A’ table contains null values.

Those who are familiar with relational database theories can appreciate the sound principles underlying those error messages. But many users of Power BI may be at a loss of what to do next. While Power BI Desktop can make creating relationships easier in the future by helping users resolve those errors automatically, there are things you can do today to connect the two tables together. The same methodology can be generalized to connect any number of tables together through a common column.

Assume a user has imported five different tables containing various information and transactions about products, all of which identify each product by a [Product] column, but the user is unable to create relationships among the tables as he or she is blocked by the errors listed above. The user wants to connect all five tables together via the [Product] column as illustrated by the conceptual schema in Figure 1.

 

Figure 1.

To do so we need to use DAX to create a calculated table in the middle so that the five original tables can each create a relationship between itself and the calculated table. I have created a mock model in this pbix file to demonstrate the technique.

Create a calculated table by clicking the New Table button as shown in Figure 2.

Figure 2.

The DAX expression is

All Products =
FILTER(
DISTINCT(
UNION(
DISTINCT(Table1[Product]),
DISTINCT(Table2[Product]),
DISTINCT(Table3[Product]),
DISTINCT(Table4[Product]),
DISTINCT(Table5[Product])
)
),
NOT(ISBLANK([Product]))
)

This DAX formula takes the unique values from the [Product] column of each of the five tables, unions them together, removes duplicate values, and finally removes the BLANK value if there is one. You can use the same formula in any model by changing the number of tables and the names of tables and columns. Now you can create five relationships between each of the five original tables and the newly created calculated table. Figure 3 shows the final result in the Relationships view. You can see that every relationship allows filters to flow both ways as indicated by  .

Figure 3.

Now that all fields in the model are connected, you can freely add any column from any table to your report, slice and dice to your heart’s content, things will just work as if by magic. Moreover, the calculated table in the middle will automatically recalculate each time you refresh data in the original tables.