Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 30

Date and Fact Table Relationship

Recommended resources

Hello! Welcome back.

In our last lesson, we built a comprehensive and well-structured Date table, enriching it with columns for year, month, quarter, and more. We also configured it to sort correctly. However, that table currently exists in isolation. It isn't connected to our actual sales data, which means it can't yet be used for analysis.

This lesson bridges that gap. Our goal is to establish and verify the relationship between our new Date table and our Sales fact table. This is the final and most critical step in setting up our data model for time intelligence. By creating this connection, we enable filters to flow from our calendar to our sales data, unlocking the power to slice our metrics by any date attribute we choose.

1. Understanding and Creating Relationships

In Power BI, a relationship is a connection between two tables that establishes how their data should be correlated. It's built on a common column that exists in both tables. In our case:

  • The Date table (our dimension table) has a unique row for every single day. The [Date] column is our unique key. This is the "one" side of the relationship.
  • The Sales table (our fact table) contains transaction records. It can have multiple sales on any given day. The [OrderDate] column holds the date of each sale. This is the "many" side of the relationship.

By connecting Date[Date] to Sales[OrderDate], we create a one-to-many relationship. This structure allows us to select a year, month, or day from our Date table and have Power BI automatically filter the Sales table to show only the transactions that occurred in that period.

Let's watch a short video that demonstrates how to create this relationship in Power BI's Model view.

Creating a simple date table in Power BI

The experts at SQLBI provide a quick, clear demonstration of creating a relationship between a date table and a sales table. They also touch on a best practice we'll cover later.

Watch from 02:59 to 03:25. Focus on how the relationship is created by dragging the date column from the date table to the order date column in the sales table.

Your Turn: Create the Relationship

Now, it's time to apply this in your Power BI file.

  1. Click on the Model view icon on the left-hand pane in Power BI Desktop. You should see your tables (Sales, Products, Date, etc.) represented as boxes.
  2. Find your Date table and your Sales table. You may need to drag them closer together for convenience.
  3. Click and hold the Date column in your Date table.
  4. Drag your cursor over to the Sales table and drop it directly onto the OrderDate column.

You should see a line appear connecting the two tables. If you hover over the line, it will highlight the two columns involved. You'll also see a 1 next to the Date table and an asterisk (*) next to the Sales table, visually confirming the one-to-many relationship.

Relationship Between Date Table and Sales Fact Table
This image shows a classic one-to-many (1:*) relationship. The unique 'DateKey' in the dimension table is linked to the 'OrderDateKey' in the fact table, where multiple orders can occur on the same date.

For a more detailed, text-based guide on creating and managing relationships, the official Microsoft documentation is an excellent resource.

Create and Manage Relationships in Power BI Desktop

This article from Microsoft provides a comprehensive overview of relationships in Power BI. We'll focus on the section for manual creation.

Briefly read the section titled 'Create a relationship manually'. This shows the alternative method of using the 'Manage relationships' dialog box, which gives you more control and is useful in complex models.

2. Verifying the Relationship

How do we know the relationship is actually working? We test it.

Without a relationship, if you try to slice a measure by a column from another table, you get nonsensical results. The measure will just show its grand total repeated for every item.

Let's verify:

  1. Go to the Report view.
  2. Create a Table visual.
  3. From your Date table, drag the Year column into the table.
  4. From your Sales table, drag your [Total Revenue] measure into the table.

If the relationship is working correctly, you will see the total revenue broken down by each year. If it's not working, you would see the same grand total for every year.

3. A Data Analyst's Troubleshooting Guide: The DateTime Trap

A very common issue that trips up many Power BI users is a mismatch in data types. Your Date[Date] column is a pure Date type. But often, data from source systems comes in as a DateTime type (e.g., 1/1/2023 10:45:13 AM).

Even though 1/1/2023 10:45:13 AM and 1/1/2023 represent the same day, they are not identical values. Because a relationship requires an exact match, it will fail to work correctly. The most confusing part is that simply changing the format of the DateTime column to a short date in the Model or Data view does not solve the problem; the underlying time data is still there.

The correct solution is to change the data type in the Power Query Editor. This permanently removes the time component, ensuring a clean match.

This video from Havens Consulting provides an excellent, in-depth explanation of this exact problem and its solution.

Relationship Issues With DateTime Data Types in Power BI

This video masterfully explains one of the most common relationship issues in Power BI. Understanding this will save you hours of frustration in the future.

Watch from 00:30 to 05:08. Pay close attention to: Why the relationship appears broken (00:30). Why simply changing the data type in the model view is the wrong approach (01:21). The crucial difference between how Power Query and the data model handle date types (02:45). The correct fix: changing the type to 'Date' within the Power Query Editor (04:10).

Take a moment to check your Sales[OrderDate] column. Go to the Power Query Editor (Transform data on the Home ribbon), select the Sales query, and ensure the OrderDate column has the Date data type icon (a calendar, not a calendar with a clock).

4. Finalizing with a Best Practice

Now that you have a verified, working relationship, there is one final best practice to implement. You should hide the original date column (OrderDate) in your Sales table.

Why? This prevents you or other report users from accidentally using it in visuals. All filtering and slicing by date should now exclusively come from your new, authoritative Date table. This enforces consistency and ensures all time intelligence calculations work as expected.

How to do it:

  1. Go to the Model view or Data view.
  2. In the Fields pane on the right, find the Sales table.
  3. Right-click on the OrderDate column.
  4. Select Hide.

The column will be grayed out, indicating it will not be visible in the Report view.

Conclusion

Congratulations! You have now fully integrated the Date table into your data model. This completes the foundational setup for all of our future time intelligence work.

Key Takeaways:

  • Relationships connect tables based on common columns, allowing filters to flow between them.
  • We established a one-to-many (1:*) relationship from our Date dimension table to our Sales fact table.
  • A common pitfall is a DateTime vs. Date data type mismatch, which must be resolved in the Power Query Editor, not by reformatting.
  • Verifying your relationship by creating a simple visual is a crucial step to ensure your model is working correctly.
  • As a best practice, hide the original date column in your fact table to enforce the use of the official Date dimension table.

Preview of the Next Lesson:
With our data model correctly structured, we are finally ready to write powerful time intelligence DAX functions. In the next lesson, we will begin calculating essential business metrics like Year-to-Date (YTD), Quarter-to-Date (QTD), and Month-to-Date (MTD) sales.