Power BI Technical Article: Data Modelling, Relationships & Joins

Introduction

Power BI transforms raw data into actionable dashboards and reports, but the quality of these outputs depends heavily on initial data preparation. Data modeling arranges tables, keys, and relationships so Power BI can efficiently process connections between dataset components. Building a proper model boosts report performance, streamlines DAX calculations, reduces redundancy, improves scalability, and makes the system easier to navigate. This article examines essential modeling schemas, fact and dimension tables, filter directions, and Power Query joins, while highlighting the key differences between merging tables in Power Query and establishing model relationships in Power BI.

Data Modelling

Data modeling in Power BI is the process of structuring tables and establishing relationships between them. Rather than consolidating everything into a single, massive flat table, data is divided into distinct logical entities linked by unique keys.
For instance, a travel agency’s dataset might be split into dedicated tables such as:

• Customers (demographics, contact details)
• Products (tour packages, flight options)
• Dates (booking dates, travel dates)
• Locations (destinations, departure points)
• Sales Transactions (revenue, payment status, bookings)

A high-quality data model ensures:

1. Accurate Reporting: Uniform results across all pages and visuals.

2.Cleaner DAX: No need for complicated formula workarounds.

3.Peak Efficiency: Faster query execution and reduced memory usage.

4.Growth Potential: Easy scaling for larger datasets and new requirements.

5.Simple Maintenance: Easier troubleshooting, updates, and future enhancements.

Power BI implements these data modelling designs using three core schema patterns: flat, star, and snowflake.

Flat table

A Flat Table schema combines all attributes, entities, and transactional measures into a single, massive table without establishing relationships across multiple entities. It is fully denormalized, meaning data repetition is intentionally accepted instead of splitting entities into separate lookup tables.

1. Table Structure

• Structure: A single table containing every column needed for analysis.
• Granularity: Each row represents a single atomic transaction or event alongside all contextual descriptive metadata.
• Redundancy: High textual redundancy across rows (e.g., customer names, addresses, product descriptions, and categories are repeated on every sales row).

Advantages:

• Simplicity: No relationships to manage, configure, or troubleshoot.
• Ease of Export: Ideal for simple reporting tools or CSV exports.
• Quick Setup: Fast to build for ad-hoc, single-purpose analysis.

Disadvantages:

• High Memory Footprint: Duplicate text values inflate file size and memory usage significantly.
• Data Anomalies: Risks update, insert, and deletion anomalies (e.g., changing a customer’s region requires updating thousands of rows).
• Slower Performance on Large Datasets: Large text fields reduce columnar compressional efficiency in analytical engines.

Appropriate Use Cases

• Simple, single-topic reporting (e.g., exporting a basic Excel sheet into Power BI).
• Small datasets (< 100,000 rows) where model architecture optimization is unnecessary.
• Ad-hoc data exploration and prototyping before formal modeling.

Star Schema

A Star Schema separates data into a central Fact Table (containing numerical, quantitative metrics) surrounded by Dimension Tables (containing descriptive context). The schema resembles a star, where the central fact connects directly to each surrounding dimension via single-key relationships.

Fact Table Structure:

Central table containing foreign keys and quantitative measurements (numerical facts like quantity, revenue, cost).
Dimension Tables: Surrounding denormalized tables containing attributes used for filtering, slicing, and grouping (e.g., Customer, Product, Date, Location).
Relationships: Direct 1-to-Many ($1:*$) single-directional relationships from Dimensions to Fact.

Advantages:

• Optimal Performance: Designed specifically for Power BI’s VertiPaq engine.

• Simplified DAX: Filter context flows naturally from dimensions to fact tables without complex code.

• High Scalability: Scales effortlessly to tens or hundreds of millions of rows.

• Intuitive Design: Easy for business analysts and report developers to understand.

Disadvantages:

• Minor Data Redundancy: Dimension tables contain denormalized hierarchies (e.g., Category stored in Product table).

• Initial Prep Required: Requires data transformation in Power Query or the data warehouse prior to reporting.

Appropriate Use Cases

• Standard enterprise analytics, business intelligence dashboards, and Power BI semantic models.

• Datasets of any medium-to-large size requiring fast visual cross-filtering and time intelligence.

• Standard practice for almost all Power BI reporting solutions.

Snowflake Schema

A Snowflake Schema is a variation of the star schema where dimension tables are normalized into sub-dimension tables. Hierarchies (such as Category, Subcategory, Product) are split into separate physical tables to remove data redundancy across dimensions.

Table StructureFact Table:

• Central table with foreign keys and quantitative values.

• Normalized Dimensions: Main dimension tables connect to secondary lookup tables rather than containing all attributes directly.

• Relationships: Chains of 1-to-Many relationships extending outward from the fact table.

Advantages:

• No Data Redundancy: Strictly normalized dimensions conserve storage space in traditional relational databases (SQL).

• Data Integrity: Updates to hierarchy attributes occur in a single place.

Disadvantages:

• Increased Complexity: Multiple tables create a web of relationships that complicate the model diagram.

• Slower Queries: Requires evaluating multiple relationship joins across tables to filter a single report visual.

• Harder DAX: Complex relationship chains can complicate filter propagation and time intelligence metrics.

Appropriate Use Cases

• Enterprise data warehouses optimized strictly for relational database storage efficiency.

• Scenarios with extremely complex, multi-level hierarchies shared across distinct business domain entities.

• When source systems enforce normalization and merging them into a Star Schema is unfeasible due to ETL constraints.

Fact Tables and Dimension Table

The fact table sits at the center of a data model. Every time a business process occurs—such as a sale, a website click, or a bank deposit—a new row is added.
Types of Data Stored:

• Additive Metrics: Numeric values you can sum across any dimension (e.g., total sales revenue, units sold).

• Foreign Keys: Numerical IDs that point directly to corresponding dimension tables.Example: In a tour company database, the FACT_SALES table records each transaction with columns like TravelDateKey, CustomerKey, PackageKey, AgentKey, and HotelKey.

Dimension Table
Dimension tables surround the fact table. They contain descriptive, textual attributes that allow users to ask “who, what, where, when, and why” about the numerical data.
Types of Data Stored:

• Attributes & Hierarchies: Detailed text descriptions (e.g., Product Name, Subcategory, Category).

• Primary Key: A unique identifier per row used to link to the fact table.Example: A DIM_CUSTOMER table holds distinct rows for every customer with details like CustomerName, Email, Gender, City, and Country.

Relationships in PowerBI

In Power BI, a relationship is a logical link established between two distinct tables based on a shared column . Instead of combining all attributes into one large, flat table, relationships define how rows in one table correspond to rows in another.

Why Relationships Are Necessary Across Multiple Tables

When data is normalized and split across multiple tables, relationships are required for the following reasons:

• Enables Cross-Filtering: Relationships allow filters applied in one table (e.g., selecting a country in Dim_Customer) to automatically flow and filter data in another table (e.g., updating the total revenue in Fact_Sales).

• Eliminates Data Redundancy: Storing descriptive data (like customer addresses ) in separate lookup tables saves massive amounts of memory compared to repeating that text across millions of transaction rows.

• Simplifies DAX Calculations: Power BI’s DAX engine relies on automatic filter context propagation through active relationships. Without them, every measure would require complex, manual lookup functions like LOOKUPVALUE() or TREATAS().

• Maintains Data Integrity: Separating core entities prevents update and deletion anomalies, ensuring a product name change only needs to happen in one place rather than thousands.

Relationship Cardinalities in Power BI

Cardinality defines the numerical relationship between the unique values in the key columns of two tables.

One-to-Many (1:) / Many-to-One (:1)

How it Works
One Side : Contains unique, non-duplicate values in the key column (Primary Key).Many Side ($*$): Contains repeating values in the corresponding key column (Foreign Key).Filter context naturally flows from the One side down to the Many side.

One-to-One (1:1)

How it Works
Both connected columns contain only unique values, with no duplicates on either side.
A row in Table A matches at most one corresponding row in Table B.

Many-to-Many (:)

How it Works
Neither column contains unique values; duplicate keys exist on both sides of the relationship.

Power BI evaluates this relationship using a non-directional or bi-directional filter context where both tables can filter each other.

Primary keys act as the unique, immutable identifiers for every row in a lookup table, establishing the target “One” side of a relationship so other tables can link directly to specific records without ambiguity. Foreign keys complement this structure by residing in fact or child tables, referencing those primary keys to allow filter context to flow seamlessly without repeating descriptive metadata across transaction rows. For these connections to work reliably, unique values are essential on the primary side, guaranteeing distinct dimension entities that eliminate relationship ambiguity and maximize Power BI’s columnar compression engine. Together, these elements determine the model’s cardinality the specific ratio of unique keys between tables—which dictates how filters propagate across report visuals and directly impacts DAX logic, query speed, and RAM consumption. Maintaining strict referential integrity ensures every foreign key points to a valid primary key, preventing orphaned rows in fact tables and avoiding the generation of unwanted “Blank” categories in reports. Finally, relationships operate as either active or inactive: active relationships serve as the default pathway for automatic filter propagation across standard measures, while inactive relationships remain dormant to prevent circular dependencies, offering secondary analytical pathways that can be activated dynamically on demand using specific DAX measures.

PowerQuery Joins

In Power Query, a join is an operation that combines two tables horizontally based on matching values in one or more key columns, producing a single merged table. This process is executed using the Merge Queries feature, where you select a primary (first) table and a secondary (second) table, specify the key columns to match on, and choose the appropriate join type to control which rows are retained.

Join Types in Power Query

• Left Outer Join How it Works: Keeps all rows from the left (first) table and matches them with corresponding rows from the right (second) table. If a row in the left table has no match in the right table, the expanded columns from the right table will display null values.

Use Case: Appending lookup information (e.g., bringing product names into a sales transaction table) while ensuring no sales records are lost, even if a product ID is missing from the product list.

• Right Outer Join How it Works: Keeps all rows from the right (second) table and matches them with corresponding rows from the left (first) table. Unmatched rows from the left table are omitted, and unmatched right table rows display null values for left-table columns.

Use Case: Analyzing master data against transactions (e.g., keeping all registered products and checking which ones have sales data from a secondary table).

• Full Outer Join How it Works: Retains all rows from both tables, regardless of whether a match exists. Where rows match, data is combined; where no match exists, null values fill the missing side.

Use Case: Reconciling two comprehensive lists (e.g., comparing last month’s customer database with this month’s database to see new, remaining, and deleted records in a single view).

• Inner Join How it Works: Retains only the rows that have a matching key in both tables. Any row from either table that lacks a corresponding match in the other table is completely filtered out.

Use Case: Filtering a dataset to analyze strictly active interactions (e.g., keeping only sales transactions for customers who are active subscribers listed in a VIP membership table).

• Left Anti Join How it Works: Retains only the rows from the left table that DO NOT have a match in the right table.

Use Case: Identifying missing or orphaned records (e.g., finding sales transactions that reference invalid or deleted product IDs that don’t exist in the main product catalog).

• Right Anti Join How it Works: Retains only the rows from the right table that DO NOT have a match in the left table.

Use Case: Spotting unused entities or inactive master data (e.g., finding products in the inventory catalog that have zero sales recorded in the transaction table).

Conclusion

For a typical business intelligence project, the optimal architectural choice is a Star Schema backed by strict One-to-Many (1:∗) single-directional relationships.
While alternative structures like Flat Tables offer quick setup and Snowflake Schemas eliminate data duplication in source databases, the Star Schema strikes the ideal balance across every critical model dimension:

Query & Report Performance:

Power BI’s VertiPaq engine is explicitly optimized for star schemas. Storing descriptive metadata in lookup dimensions and high-cardinality numeric metrics in central fact tables maximizes columnar compression and enables high-speed query execution with minimal memory usage.

DAX Simplicity & Filter Propagation:

A Star Schema provides a clean, single-directional filter context that flows predictably from dimension tables down to fact tables. This eliminates the need for complex, performance-heavy DAX workarounds like CALCULATE().

Model Readability & Ease of Use:

The straightforward “center-and-spokes” layout creates an intuitive visual diagram in Power BI’s Model View. Report builders can easily identify where to source slicers and visual headers (Dimension tables) versus aggregated metrics (Fact tables).

Controlled Redundancy vs. Complexity:

Unlike a Snowflake Schema, which introduces a complex web of normalized tables, a Star Schema accepts minor, controlled redundancy within dimension tables (e.g., keeping Category and Subcategory in a single Product table) to eliminate relationship complexity and streamline join processing.

Scalability & Long-Term Maintainability:

As datasets grow into tens of millions of rows, a Star Schema scales efficiently. New business domains can be integrated as standalone dimension tables without disrupting existing relationship logic, making the entire solution easier to audit, update, and maintain over time.