It's finally time to create a relationship between the temperature table and internet sales table. The key on the Temperature table is a combination of the region name and the month number of the year. This column combination makes a single row unique in this table, as shown in the following screenshot:

Figure 6-Column combination that makes a single row unique

Unfortunately, neither of those two columns currently exist in the Internet Sales table. However, the Internet Sales table has a relationship to the Sales Territory table, and the Sales Territory table has the region. Therefore, you can determine the region for each sale by doing a simple lookup operation. Well, it should be that simple, but it's not quite that easy. Let's take a look at why.

Calculated columns do not automatically use the existing relationships in the data model. This is a unique characteristic of calculated columns; calculated measures automatically see and interact with all relationships in the data model. Now let's take a look at why this is important.

In the following screenshot, I have created a new column on the Internet Sales table and I am trying to return the region name from the Sales Territory table. Take a look at the following screenshot:

Figure 7-Sales Territory table

Note that there is no IntelliSense, and that the autocomplete functionality is unavailable as I type in "Sales Territory". The reason for this is because the calculated column cannot see the existing relationships in the data model, and therefore does not automatically return the column you want from another table. There is a much more complicated explanation behind all this, but for now, suffice to say that navigation functions (RELATED and RELATEDTABLE) allow calculated columns to interact with and use existing relationships.

If I rewrite the following DAX formula with the RELATED function, then you will notice that IntelliSense has returned, along with the autocomplete functionality that was previously discussed:

Figure 8-Temperature key column

Now it's time to create a Temperature Key column on the Internet Sales table. Create a new column on the Internet Sales table and then type in the following DAX formula:

Figure 9-Temperature Key column on the Internet Sales table

Now that the temperature key has been created on the Internet Sales table, let's create the relationship. Click Manage Relationships from the home ribbon and then click New... to open the Create Relationship window. Then complete the following steps to create a new relationship. The relevant fields and entries for each step are marked out on the following screenshot:

Select Internet Sales from the first drop-down selection list
Select the Temperature Key from the list of columns
Select Temperature from the second drop-down selection list (scroll right)
Select Key from the list of columns

Click OK to save your new relationship:

Figure 10-Creating new relationship