Introduction
To master Power BI, you need to understand how data is structured, connected, and stored. Here is a comprehensive, structured guide to Modelling, Joins, Relationships, and Schemas in Power BI.
Data Modeling in Power BI
Data modeling is the process of identifying, organizing and defining the types of data a business collects and the relationships between them. It uses diagrams, symbols and textual definitions to visually represent how data is captured, stored and used. A well-designed data model helps:
- Understand data requirements
- Ensure proper structure for reporting
- Align with business goals
- Maintain data integrity
This is the golden rule of Power BI. While you can use a single flat table, it is inefficient and leads to poor performance. The recommended model is the Star Schema.
It consists of:
Fact Table (The Center): Contains quantitative data (numbers you want to aggregate). Examples: Sales Amount, Quantity Sold, Profit. Fact tables have foreign keys that link to dimension tables.
Dimension Tables (The Points): Contains descriptive attributes (text/categories you want to slice by). Examples: Customer Name, Product Color, Date, Location. Dimension tables should have a unique primary key.
Why?
Star schemas reduce memory usage, speed up calculations, and make DAX formulas (like CALCULATE) much easier to write.
Relationships (Connecting tables)
Relationships are the main feature of data modelling defining all data types. Relationships helps connect with multiple data sources using cardinality.
Relationships in Power BI define how tables filter each other. You manage them in the Model View.
Cardinality (The "One" and "Many")
Many-to-One (*:1): The most common and preferred. One row in the Dimension table relates to many rows in the Fact table (e.g., one
ProductIDappears many times in the Sales table).One-to-One (1:1): Rare. Used when splitting a wide table for security or performance.
Many-to-Many (:): Avoid if possible. Used when bridging two fact tables or when dimensions are not unique (e.g., a product can belong to multiple categories). It creates ambiguity and slows down performance.
Cross-Filter Direction (The Arrow)
Single Direction (→): The default. Filters flow from the "One" side (Dimension) to the "Many" side (Fact).
Both Directions (↔): Use sparingly. This allows filters to flow from Fact to Dimension, which is useful for "Row-Level Security" but can create ambiguous path errors (circular dependencies).
Active vs. Inactive Relationships
You can have multiple paths between two tables, but only one can be active at a time.
To use an inactive relationship (dotted line), you must activate it in a DAX measure using the USERELATIONSHIP function (e.g., switching between Order Date and Ship Date).
Types of Joins (How Power BI combines data)
Unlike SQL, Power BI does not use JOIN in the query editor to combine tables for modelling (you use Relationships instead). However, when you merge queries in Power Query, you use standard joins:
| Join Type | Description | Power BI Use Case |
|---|---|---|
| Left Outer | Keeps all rows from the left table; matches from the right. | Adding City names to a Customer list. |
| Right Outer | Keeps all rows from the right table; matches from the left. | Rarely used (just swap the tables). |
| Inner | Keeps only rows that exist in both tables. | Filtering out inactive customers by joining Sales to Customers. |
| Full Outer | Keeps all rows from both tables. | Merging two separate branch lists together. |
| Anti (Left/Right) | Keeps rows in the left that have no match in the right. | Finding customers who haven't placed an order in 12 months. |
Best Practice: Do as many merges/joins as possible in the source database (SQL), not in Power Query, as Power BI's merge engine is memory-intensive.
Different Schemas in Power BI
A "Schema" is the overall blueprint of your tables and relationships.
Star Schema (Recommended)
- Structure: 1 Central Fact + Multiple Dimensions.
- Pros: Fastest performance, easiest DAX, simplest filtering.
- Cons: Requires time to normalize your data.
Snowflake Schema
- Structure: Dimensions are further normalized into sub-dimensions.
- Pros: Saves storage space (less data duplication).
- Cons: Slower in Power BI. It creates more relationships, forcing the engine to traverse multiple tables to apply a single filter. Power BI recommends against snowflaking; denormalize your dimensions into a single flat table if possible.
Flat Schema (Single Table)
- Structure: A single massive table with all columns (facts and dimensions) combined.
- Pros: No relationships needed; simple for beginners.
-
Cons: Massive file size. DAX measures become complex (you can't use
CROSSFILTER). Lack of date tables makes time intelligence impossible. Avoid for enterprise reports.
Best Practices for Power BI Modeling
Hide Foreign Keys: In the Model view, hide the foreign key columns in your Fact table so report builders don't accidentally use them as filters.
Create a separate Date Table: Never use a "datetime" column as your date. Create a dedicated calendar table (with Year, Month, Quarter, Weekday) and mark it as "Date Table" in Power BI. Connect it to your fact table(s) using a single-direction relationship.
Sort By Column: If you have a text month (Jan, Feb), use the "Sort by Column" feature to sort it by a numeric Month Number column.
Referential Integrity: Ensure your Foreign Keys in the Fact table do not contain values that don't exist in the Dimension table. Power BI handles this by creating a blank "Unknown" row, which can skew totals.
Set the Datatype: Always set numeric columns to Decimal or Whole Number (never Text), and set date columns to Date (not Date/Time) unless you need timestamps.