Importing the date table

In this section, we are going to import a date table to support analyzing data based on when an order shipped. From the get data option, select excel and open the AdventureWorksDW excel file; the file can be found in the directory location, C:\Packt\Power BI Quick Start\Data\

Next, select DimDate from the list of tables, and then click load:

Now that the data has been imported, the next step is creating a valid relationship. Select Manage Relationships, found on the home ribbon, to launch the relationship editor. Click new to create a new relationship. Complete the following steps:

1. Select FactInternetSales from the drop-down list.
2. Select the ShipDate column; use the scroll bar to scroll all the way to right.
3. Select DimDate (2) from the drop-down list.
4. Select the FullDateAlternateKey column.
5. Click OK to close the create relationship window.

I took the liberty of changing the table and column names here, for clarity. You will learn how to rename tables and columns in the following Usability enhancements section.

1. DimDate has been renamed Order Date.
   
2. DimDate (2) has been renamed Ship Date.

The data model now has two date tables, each with an active relationship to the FactInternetSales table. If you wish to see sales by order year then you would bring in the year column from the Order Date table, and if you wish to see sales by the ship year, then you would bring in the year column from the Ship Date table:

Importing the same table multiple times is generally the preferred method when two tables have multiple relationships between them. This method is easy to explain to end users and allows you to reuse most, if not all, of your existing DAX calculations.

The alternative method is to create inactive relationships and then create new calculations (measures) using the Data Analysis Expression (DAX) language. This method of leveraging inactive relationships can become overwhelming from an administrative point of view. Imagine having to create copies of the existing measures in the data model for each relationship between two tables. In the current data model, FactInternetSales stores three dates, and this would possibly mean having to create and maintain three copies of each measure, one to support each date.