Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Connect Two Fact Tables in Power BI

Cody Schneider••8 min read

Connecting two different sets of numbers in Power BI, like your sales data and your website traffic data, feels like something that should be simple. You have a Sales table and a Traffic table, and you just want to see how they relate. But if you've ever tried to drag a relationship line between two "fact tables" like these, you've likely run into murky results or Power BI outright preventing the relationship. This is a common and frustrating hurdle, but it's happening for a good reason that protects the integrity of your data model.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

This tutorial will explain why directly connecting fact tables is a bad practice and walk you through the correct, stable, and scalable method using a star schema with shared dimensions. We'll build a working model, step by step, that lets you accurately analyze different parts of your business in one cohesive report.

Why You Shouldn't Directly Connect Fact Tables

Before jumping into the "how," it's important to understand the "why." Connecting two fact tables directly on a column they share, such as a 'Date' column, might seem logical. A sale happened on a specific date, and a website session happened on a specific date, so why not link them?

The core issue is ambiguity and incorrect filtering. Let's imagine an example:

  • Sales Fact Table: Contains a row for every single order. On January 1st, you had 100 orders. This table has 100 rows for that date.
  • Support Tickets Fact Table: Contains a row for every support ticket created. On January 1st, 5 tickets were created. This table has 5 rows for that date.

If you create a direct relationship between Sales[OrderDate] and SupportTickets[TicketDate], what happens when you try to filter your data for January 1st?

Power BI's engine gets confused. Which table should filter the other? If you filter by a sales record, does that mean you should only see a fraction of the tickets? Or if you select a ticket, which of the 100 sales from that day should it show? This creates a messy many-to-many relationship that is computationally expensive and almost certainly returns the wrong numbers. Your report will show inflated or nonsensical aggregates because the filtering logic is broken.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Introducing the Star Schema

The industry-standard solution to this problem is a "star schema." It sounds technical, but the concept is simple. Instead of a spiderweb of connections, you organize your data model into two types of tables:

  • Fact Tables: These tables contain the things you measure - the "facts" or events that happen. Examples include sales transactions, website sessions, ad clicks, or support tickets. They are typically narrow and long, with lots of numbers and numerical keys.
  • Dimension Tables: These tables contain the descriptive context - the "who, what, where, and when" behind the facts. Examples include a Customers table (who bought), a Products table (what was bought), and a Calendar/Date table (when it was bought). They are typically wide and short compared to fact tables.

In a star schema, your fact tables never talk to each other directly. Instead, they each connect to shared dimension tables. It creates a model that looks like a star, with fact tables in the center linked out to their descriptive dimensions.

The Solution: Using Shared Dimensions to Connect Fact Tables

The proper way to relate two fact tables is through one or more common dimension tables. If Fact_Sales and Fact_Traffic both connect to the same Dim_Date table, then the Dim_Date table acts as a bridge. When you select a date from Dim_Date, it correctly filters both sales and traffic for that specific date.

Let's make this practical with a common business scenario: you want to build a Power BI report showing your daily Shopify sales alongside your daily website sessions from Google Analytics.

Step 1: Identify Your Fact and Dimension Tables

First, load your data into Power BI. You'll likely have these two key tables from your sources:

  • Shopify Orders Table (Fact Table): Contains one row per order, with columns like OrderID, OrderDate, CustomerID, ProductID, and TotalSales.
  • Google Analytics Sessions Table (Fact Table): Contains daily aggregated data, with columns like Date, Source/Medium, DeviceCategory, and Sessions.

Notice a problem? We want to connect them using Date, but there's no single, clean "Date" table that both of them can reference. We need to create one.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Step 2: Create a Shared Date Dimension Table

A dedicated Date table is the most common and powerful shared dimension. It acts as the single source of truth for all time-based analysis. Even if your fact tables already have date columns, a separate Date dimension is best practice because it allows you to add useful columns for filtering like 'Month Name', 'Quarter', 'Day of Week', etc.

You can create a Date table in Power BI using DAX (Data Analysis Expressions).

  1. Go to the Data view in Power BI (the table icon on the left).
  2. In the top ribbon, select the Table tools tab and click New Table.
  3. Enter the following DAX formula. This code creates a table with a single column called 'Date' that contains a list of every date between the earliest order date in your sales data and the latest session date in your traffic data.
Date Dimension =
CALENDAR (
    MIN ( 'Shopify Orders'[OrderDate] ),
    MAX ( 'GA Sessions'[Date] )
)

After creating this basic table, you can add more descriptive columns to it for better reporting. With the Date Dimension table selected, click New Column in the ribbon for each of the following:

  • Year: Year = YEAR ( 'Date Dimension'[Date] )
  • Quarter: Quarter = "Q" & FORMAT ( 'Date Dimension'[Date], "Q" )
  • Month Number: Month Number = MONTH ( 'Date Dimension'[Date] )
  • Month Name: Month Name = FORMAT ( 'Date Dimension'[Date], "mmmm" )

Finally, to ensure Power BI recognizes this as your primary date table, right-click on the Date Dimension table in the Fields pane, hover over Mark as date table, click it, and select the Date column when prompted.

Step 3: Build the Relationships in the Model View

Now it's time to create the connections.

  1. Go to the Model view in Power BI (the icon with three connected boxes on the left).
  2. Arrange your tables so that your new Date Dimension table is between your two fact tables: Shopify Orders and GA Sessions.
  3. Click and drag the Date column from the Date Dimension table and drop it onto the OrderDate column in your Shopify Orders table.
  4. Then, click and drag the Date column from the Date Dimension table again and drop it onto the Date column in your GA Sessions table.

You should now see two relationships extending from your Date dimension - one to each fact table. Notice the "(1)" on the Date Dimension side and the "(*)" on the fact table side. This indicates a proper one-to-many relationship. For any single date in your dimension table, there could be many sales or many sessions in your fact tables.

Your data model now looks clean and follows the star schema principle. The two fact tables are linked indirectly via the shared dimension.

Note: You can repeat this process for other shared dimensions. For example, if both tables had a 'Country' column, you could create a dedicated Country Dimension table and connect both fact tables to it.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 4: Create Measures for Analysis

While you can sometimes just drag numerical values onto visuals, it’s best practice to create explicit measures. This gives you more control and ensures your calculations are always correct.

  1. Go back to the Report view (the bar chart icon).
  2. In the Fields pane, right-click on your Shopify Orders table and select New Measure.
  3. Enter this formula: Total Sales = SUM ( 'Shopify Orders'[TotalSales] )
  4. Right-click on your GA Sessions table and select New Measure.
  5. Enter this formula: Total Sessions = SUM ( 'GA Sessions'[Sessions] )

Step 5: Visualize Your Combined Data

This is the moment of truth. Now you can build visuals that combine data from both tables, filtered correctly by your new Date dimension.

  1. Add a new Line chart to your report canvas.
  2. From your Date Dimension table, drag the Date field onto the X-axis well of the visual.
  3. From your Shopify Orders table, drag your new [Total Sales] measure onto the Y-axis well.
  4. From your GA Sessions table, drag your new [Total Sessions] measure also onto the Y-axis well.

You will now see a chart with two lines: one representing your total sales and one representing your total sessions over time. You are now analyzing data from two separate fact tables in a single, synchronized visual. You can also use other columns from your Date Dimension table as slicers or filters (e.g., filter by 'Month Name') and they will correctly filter both measures simultaneously.

Final Thoughts

By resisting the urge to connect fact tables directly and instead using shared dimensions, you build a data model that is robust, scalable, and produces accurate results. This star schema approach, centered on a Date dimension, is the foundation of powerful and reliable reporting in Power BI, allowing you to blend diverse datasets from across your business to uncover deeper insights.

While mastering data modeling in Power BI is a powerful skill, we know it can introduce friction for teams that just need fast answers from their data. We built Graphed to remove this exact barrier. You simply connect your data sources like Shopify and Google Analytics, and use natural language to ask questions like "Show me a chart comparing daily sales and website sessions for the last 90 days." Our AI handles the relationships and data modeling in the background, instantly generating the real-time dashboards you need without you ever having to worry about star schemas or DAX.

Related Articles