Introduction
When I first heard about Power BI, I thought it's all about making charts and dashboards. I came to realize even the most beautiful visuals can produce wrong numbers when it's not well organised and most beginners start questioning themselves why their totals are not adding up correctly.
Data Modeling
Data modeling is organising and structuring your data so that it can be efficient and accurate for Power BI to easily analyse it.
In simple terms, modeling is what separates a report that looks good from what is actually correct. You can think of it like a building plan. You cannot skip the plan and expect the building to go well. If you skip data modeling, you will get inaccurate report.
Joins
Joins in Power BI combines data from multiple tables based on related columns. It uses relationships and DAX for joining data unlike SQL where you write join statements.
1. Inner Join
An inner join returns only the rows where both tables have a matching value and leaves out the ones that don't have a match. This join gives you a clean and trimmed result with no missing pieces on either side.
2. Outer Join
A full outer join returns all rows from both tables. Where there is a match, the columns are filled in from both sides. Where there is no match on either side, you get nulls.
You can only use it when you need a complete picture of both datasets.
3. Left Join
A left join returns all rows from the first table and the matching rows from the second table. If a row in the left table has no match in the right table, the right-side columns come back as null. It acts as 'don't leave anyone behind.'
4. Right Join
A right join is the exact opposite of the left join. It returns all rows from the right table and the matching rows from the left table. Unmatched rows on the left side come back as null.
5. Right Anti Join
This join keeps only the rows from the right table that don't match anything on the left. Commonly used to find new data on the second table that don't match with the main dataset.
6. Left Anti Join
A left anti join returns only the rows from the left table that have no matching rows in the right table. Used to find the 'what's missing.'
Power BI Relationships
Power BI understands how tables get connected.When you connect two tables in Power BI, that connection is called a relationship.
1. Cardinality
Cardinality describes how many rows on each table can merge. It helps you understand the structure of your data and how to join the tables perfectly.
a. 1:1
This matches one-to-one whereby each row in table A matches exactly one row in table B.
b. 1:M
In this case, one row in table A matches many rows in table B. They are most efficient and predictable in Power BI and I recommend to always aim for 1:M.
c. M:M
Multiple rows in table A can match multiple rows in table B.
This relationship can cause unexpected filter behaviour, double counting, and you can resolve it by introducing a bridge table.
d. M:1
This relationship, many rows in table A matches to 1 row in table B.
Active vs Inactive
Tables can be connected in different ways when building data models.
An active relationship is always the default and always on between two tables and appears as a solid line in model view. Every DAX and every visual in your report uses the active relationship automatically.
Inactive relationship is a stand-by connection ignored by default. You can create it only when you need a second or third path between two tables.
Schemas
Schemas simply is the structure of your data model. It shows how tables are arranged and connected to each other. You can think of it like a map to your data.
1. Star Schemas
This is the recommended standard for Power BI.
It takes one flat table and splits it into separate, clean tables, one fact table in the middle surrounded by dimension tables hence resembling a star.
2. Snowflake Schema
This is basically like a star schema where some of the dimension tables are broken down further into smaller tables.
Conclusion
I used to create dashboards that had weird numbers and calculations that I couldn't even explain. Data modeling made Power BI make sense to me and once the model was right, everything started to fall in place. Once you understand joins, relationships and schemas, your reports start telling accurate story and not just creating beautiful visuals.
Top comments (0)