Often not, most beginners assume that having clean data, visually appealing charts, and well-thought-out dashboards is the most important part of data analysis, only to realize that the quality of analysis largely depends on the organization of the underlying data. You could still get the wrong numbers if the data model is broken.
Power BI is a very powerful tool. What you'll see on a report page is the output of the charts and KPI cards. The action is in the model: what you are doing with your tables, how they interact, and how filters flow through them.
That is where concepts such as modelling, joins, schemas, and relationships come in. This article will help us understand how they relate to one another.
What is Data Modelling
Data modelling is the process of structuring data into tables, defining how they connect, and ensuring that Power BI can accurately filter data from all the tables. It is basically creating a blueprint for your data before building dashboards and doing reports. A good data model ensures:
- Reports are easy to build
- Calculations return accurate results
- Improved performance
Data models are usually viewed under the Model View of Power BI.
Power Query handles data preparation: cleaning, shaping, and joining raw data.
The Data Model is where structure and relationships thrive.
Reports are just the window you look through to see the result.
Schemas
A Schema is defined as the structure of the tables: the number of tables present, the type of tables, and their arrangement. There are two commonly used schemas: a star schema and a snowflake schema.
Star Schema
It consists of one fact table at the centre, containing measurable quantitative data(sales figures, order IDs) and several dimension tables containing descriptive data (customer names, locations, and products).
The fact table is usually wide with many rows, foreign keys and numeric values. The dimension tables are narrow with few rows and descriptive attributes.
Snowflake Schema
A Snowflake schema is an extension of the star schema where the dimension tables are further broken down into additional tables. They are normalised, hence eliminating redundancy.
Joins
A join combines two tables based on a common column.
In Power BI, joins are used when combining tables in Power Query through the merge query feature.
Types of joins
Assume you have the following two tables;
| Join Type | Description | Example Result |
|---|---|---|
| Inner Join | Returns only rows that have matching values in both tables. | Customers with orders only. |
| Left Outer Join | Returns all rows from the first (left) table and matching rows from the second table. Non-matching rows from the second table appear as null. | All customers, including those with no orders. |
| Right Outer Join | Returns all rows from the second (right) table and matching rows from the first table. Non-matching rows from the first table appear as null. | All orders, even if customer information is missing. |
| Full Outer Join | Returns all rows from both tables. Matching rows are combined, and non-matching rows contain null values where data is missing. | Complete list of customers and orders. |
| Left Anti Join | Returns rows from the first table that do not have matching values in the second table. | Customers who have never placed an order. |
| Right Anti Join | Returns rows from the second table that do not have matching values in the first table. | Orders with no corresponding customer records. |
Understanding relationships in Power BI
Relationships inform Power BI how tables are connected. They allow information stored in different tables to work together. They are the connections you define between tables in the model view.
For example, a customer may have multiple orders, and each order may contain different products.
Relationships help connect with multiple data sources using cardinality.
Cardinality specifies how the rows in one table are related to the rows in another table.
One-to-many relationship
It is a connection between two tables in a data model where one unique value in one table can be associated with multiple values in the other table. For example, customers and products: each customer can make multiple purchases of a product, so there is a one-to-many relationship between them, but each purchase(many) is associated with only one customer(one).
Many-to-many relationship
Many-to-many relationships exist when both sides of a join can have multiple matching rows.
One-to-One relationship
Usually appears when you've deliberately split a large table for performance reasons.
One record corresponds to exactly one record in another table.
Conclusion
It's tempting to treat data modelling as a box to tick before you get to the "real" work of building visuals. But the truth is, your model is the real work. Everything else is built on top of it.
Schemas give your model structure. A well-designed star schema keeps your DAX simple and your queries fast. As your dataset grows, a clean schema won't collapse under the pressure.
Joins in Power Query let you shape and integrate data at the source, before it ever enters the model. Understanding the difference between join types means you pull exactly the data you need.
Relationships are the connections that make your report interactive. When a user clicks a year on a timeline and every visual updates accordingly, that's relationships doing their job. Get them wrong, wrong cardinality, wrong direction, and your filters either don't work or produce numbers that look right but aren't.
Cross-filter direction might seem like a minor configuration choice, but it has real consequences for how filter context propagates through your model and, by extension, how every single measure in your report behaves.
Together, these four concepts form the foundation of effective data analysis in Power BI. As I continue learning Power BI, I have come to appreciate that good analysis starts long before creating charts; it begins with building the right foundation.
`
Top comments (0)