Hello! Welcome back to your journey toward becoming a top-tier FP&A analyst.
In our last lesson, we mastered the Power Query Editor, transforming messy, raw data into clean, structured tables. You learned how to automate data preparation—a massive step up in efficiency. However, those clean tables are currently sitting in our Power BI model like isolated islands of information.
Today, we will build the bridges between those islands. This lesson covers how to create relationships between data tables to build an integrated model for analysis. This is the architectural work of business intelligence. By connecting our tables in a logical way, we unlock the ability to perform sophisticated analysis—like slicing monthly revenue (from a sales table) by product category (from a products table) and comparing it against a marketing budget (from a budget table). This step transforms a simple collection of data into a powerful, interactive analytical engine.
1. The Blueprint: Understanding Data Models and the Star Schema
Before we start connecting tables, we need a blueprint. In Power BI, the most effective and widely-used blueprint is the star schema. This design isn't just an aesthetic choice; it's fundamental to performance, clarity, and ease of use.
A star schema organizes your tables into two types:
- Fact Tables: These tables contain the "facts" or transactional data you want to measure. For FP&A, this is typically your core financial data: daily P&L transactions, monthly cash flow entries, payroll details, etc. These tables are often long (many rows) and contain numeric values that can be aggregated (e.g.,
Sales Amount,COGS,Headcount). - Dimension Tables (or Lookup Tables): These tables provide the context or "who, what, where, when, why" for your fact tables. They describe the entities in your business: a
Calendartable, aProductstable, aCustomerstable, or aChart of Accountstable. They are usually wider and shorter than fact tables.
The "star" emerges when you place a fact table in the center and connect your dimension tables to it, resembling a star. Filters flow from the dimension tables "downhill" to the fact table. For example, selecting '2024' in your Calendar dimension table will filter your Sales fact table to show only sales from 2024.
Financial Reporting With Power BI
To start, let's solidify these foundational concepts. This guide from BSD Insight provides a clear explanation of the different table types and how they should be organized.
Please read the section 'Planning Your Data Model > Power BI Data Structure' (on page 3) and review the 'Pro Tips' and diagrams in the 'Model Organization' section (on page 14). Focus on understanding: The distinction between fact tables and lookup tables. The recommended 'waterfall' structure where filters flow from lookup tables at the top to fact tables at the bottom.
This structured approach is essential for building scalable models. As your analysis grows more complex, a clean star schema will prevent confusion and calculation errors.
2. Building the Model: Creating Relationships in Power BI
With our blueprint in mind, let's move into Power BI's Model View. This is where you visually define the relationships between your tables. After loading your data, you'll see your tables as separate boxes. Our job is to draw the lines that connect them.
A relationship connects two tables using a common column, often called a key. For example, you might link a Sales table and a Products table using the Product ID column that exists in both.
You can create a relationship by simply dragging the key column from one table and dropping it onto the corresponding key column in the other table. Alternatively, you can use the "Manage relationships" button in the ribbon.
Let's break down those properties:
- Cardinality: This defines the nature of the relationship between the two tables. The most common and desirable type is One-to-Many (
1:*). In our example, theProductstable has one row for each unique product, while theSalestable can have many rows for that same product. This is a one-to-many relationship. While other options exist (one-to-one, many-to-many), you should almost always aim for one-to-many. - Cross-filter direction: This determines how filters flow. For a star schema, you should always use Single. This means the "one" side of the relationship (the dimension table) filters the "many" side (the fact table). This one-way flow is predictable and efficient.
Avoiding common pitfalls is crucial. This article provides a clear, practical example of setting up relationships correctly in an FP&A context and warns against problematic relationship types.
Please read the subsection 'Step 3b. Creating Relationships for Financial Reporting.' Notice how the central 'Calendar' dimension table is connected to multiple fact tables (P&L, Cash Flow, etc.). Pay close attention to the specified cardinality (1:*) and direction (Single).
Test your understanding!
You have imported a Payroll Roster table (fact table) containing employee-level salary details for each month, and a Departments table (dimension table) that lists all company departments. Both tables contain a Department Name column. The Departments table has a unique entry for each department.
What is the correct cardinality for the relationship between these two tables, and which table should filter which?
Show answer
The correct relationship is One-to-Many (1:*) from the Departments table to the Payroll Roster table.
Reasoning: The Departments table has one unique row for each department. The Payroll Roster table can have many rows corresponding to that same department (one for each employee in that department). The filter direction should be Single, where the Departments table (the "one" side) filters the Payroll Roster table (the "many" side). This allows you to select a department name and see all the payroll data associated with it.
3. Advanced Scenarios: Inactive and Virtual Relationships
In the real world of financial data, you'll encounter situations that don't fit the simple model perfectly. Power BI provides elegant solutions for these.
Scenario 1: Multiple Dates in a Fact Table
Imagine your sales fact table has an OrderDate, a ShipDate, and a PaymentDate. You want to analyze sales by all three. However, Power BI only allows one active relationship between two tables. If you link your Calendar table to OrderDate, you can't also link it to ShipDate.
The solution is to create one active relationship (e.g., to OrderDate) and then create inactive relationships for the others. These inactive relationships are shown as dotted lines in the Model View. They don't do anything by default, but you can "turn them on" for a specific calculation using a DAX function called USERELATIONSHIP.
Scenario 2: Mismatched Granularity
As you know from your finance studies, different parts of the financial statements have different natures. An Income Statement reflects a period of time, while a Balance Sheet is a snapshot at a point in time. This can create modeling challenges. For instance, you might not want to create a direct, physical relationship between your Calendar table and your Balance Sheet table, as it can lead to incorrect filtering.
For these cases, you can use virtual relationships. Instead of drawing a physical line in the Model View, you use a DAX function like TREATAS to create a relationship on-the-fly, just for a specific calculation.
Financial Reporting With Power BI
Let's return to the 'Financial Reporting With Power BI' guide to see how these advanced techniques are applied. These concepts separate basic modelers from expert ones.
First, review the 'PRO TIPS' on page 8 in the 'Data Model Development' section, which discusses using inactive relationships and the USERELATIONSHIP function. Then, read the section 'Data Managing > Different Granularities' on page 24. Focus on how it recommends avoiding direct relationships for balance sheet data and instead using virtual relationships with the TREATAS function.
Don't worry about mastering the DAX syntax right now. The key takeaway is to know that these tools exist to handle the complex scenarios you will inevitably face in FP&A. This is a perfect example of how modeling structure and calculation logic (DAX) work hand-in-hand.
Conclusion
You have now learned how to assemble your cleaned data tables into a cohesive and powerful data model. This is the hidden architecture that drives insightful and interactive reporting. A well-structured model is not only efficient but also intuitive, allowing you and other stakeholders to easily explore the data and trust the results.
Key Takeaways:
- A star schema, with fact tables at the center and dimension tables surrounding them, is the best practice for Power BI data modeling.
- Relationships connect tables using common key columns, enabling filters to propagate across your model.
- Always aim to create One-to-Many (
1:*), Single-direction relationships, where dimension tables filter fact tables. - Avoid Many-to-Many and bi-directional relationships, as they can cause ambiguity and performance issues.
- For complex scenarios, use inactive relationships (activated with
USERELATIONSHIP) for multiple dates, and virtual relationships (created withTREATAS) for mismatched granularities like balance sheet data.
Preview of the Next Lesson:
We've built the skeleton of our model. In the next lesson, we will add the muscle by learning the basics of DAX (Data Analysis Expressions). You'll learn how to write formulas to create custom calculations and Key Performance Indicators (KPIs). We'll see exactly how functions like USERELATIONSHIP work in practice and begin building the core metrics that every FP&A analyst needs.