DATA MODELLING in Power BI
Understanding relationship, schema &
performance
About Data Modelling
Process of structuring data for analysis
Defines how tables connect & interact
Backbone of Power BI reports
Ensures accurate calculations
Importance of Data Modelling
Improves report performance
Avoids ambiguity in relationships
Enables correct DAX calculations
Makes data easy to understand
Scales well for large datasets
Fact Table & Dimension Table
FACT TABLE DIMENSION TABLE
Contains measurable values (sales, Contains descriptive data
revenue, quantity) (customer, product, date)
Large in size Smaller in size
Has foreign keys Connected to fact table
Used for calculations Used for slicing & filtering
Star Schema & Snowflake Schema
STAR SCHEMA SNOWFLAKE SCHEMA
Fact table is at the center Fact table connects to dimension
tables
Connected directly to multiple Dimension tables are further split
dimension tables into sub-dimension tables
Simple structure, easy to More complex structure
understand
Faster performance in Power BI Slower than star schema in Power
BI
Recommended for reporting Uses normalization
Relationships in Power BI
A relationship defines how two tables are connected using a
common column (key). It allows Power BI to understand
how data in one table is related to data in another table.
Example: Customer[CustomerID] ↔ Sales[CustomerID]
Why relationships are needed:
To combine data from multiple tables
To enable filtering between tables
To make DAX calculations work correctly
CARDINALITY
Cardinality defines the nature of the relationship between
two tables based on data uniqueness.
Types of Cardinality –
1. One-to-Many – One row in the dimension table is
connected to many rows in the fact table.
2. Many-to-One – One row in the fact table is connected to
many rows in the dimension table.
3. One-to-One – One row in Table A matches one row in
Table B
4. Many-to-Many – Multiple rows are matching, in both
tables
Cross Filter Direction
Cross filter direction defines how filters flow between
related tables in Power BI.
Types of Cross Filter Direction-
Single Direction – Filter flows from Dimension table to the
Fact Table
Both Direction – Filter flows from both ways between the
tables
Active & Inactive Relationship
Active Relationship- An active relationship is the default
relationship between two tables that Power BI uses
automatically for filtering and calculations.
Inactive Relationship- An inactive relationship is an
additional relationship between the same two tables that
is not used by default.
Charactertics-
ACTIVE RELATIONSHIP INACTIVE RELATIONSHIP
Solid line in Model View Dotted line in Model View
Used by default in visuals & measures Not used automatically
Automatically propagates filters Activated manually
No DAX needed to activate Used when multiple date columns exist
Primary Key & Foreign Key
Primary Key – A column that uniquely identifies each row
in a table.
Foreign Key – A column that refers to the primary key of
another table to create a relationship.
Primary Key – Unique Identifier
Foreign Key – Linked to another table’s key
Normalization & Denormalization
Normalization- It is the process of organizing data into
multiple related tables to remove redundancy and improve
data integrity.
Denormalization- It is the process of combining normalized
tables into fewer or single tables to improve query
performance and simplify reporting.
Normalization Denormalization
Removes Redundancy Combines tables
Data stored in multiple related tables Faster reporting
Good for storage, not reporting Preferred in Power BI models
Ambiguity in Data Modelling
Ambiguity occurs when Power BI finds more than one
possible path to filter data between tables and cannot
determine which path to use.
Causes of Ambiguity-
Bi-directional (both) cross filter
Many-to-many relationships
Multiple relationships between same tables
Snowflake schema
Bridge tables not configured properly
Data Model vs DAX
Data model defines structure
DAX uses model relationships
Poor model = complex DAX
Model View in Power BI
Shows table relationships
Used for managing schema
Visual representation of model
Data modelling = foundation of Power BI
Correct relationships = correct results
Good model = faster reports + simple DAX