I started learning Power BI backwards. I jumped straight into building visuals, dragging fields onto a canvas, and then kept wondering why my numbers are wrong. Well, the reason is that I had completely skipped data modelling.
Data modelling-is the invisible architecture behind every Power BI report.
Here is what you need to understand:
What is a Data Model?
A data model is the structure that defines how your tables relate to each other. In Power BI,there is a tool called the Model View, where you connect tables using relationships.
Its often depicted as lines that tell Power BI "this column in this table matches that column in that table."
The most important concept in data modelling is the difference between two types of tables:
Fact table-They contain measurements i.e. numbers that change over time e.g. sales amounts.
Dimension table-They provide context i.e. the who, what, when and where e.g. Customer names, product categories, customer locations etc.
Star Schema-It is a data modeling technique that organizes data into a central fact table connected to multiple dimension tables, optimizing query performance and simplifying analysis.
*Power BI works best with this structure. Your reports will be faster, your filters will work correctly, and your calculations will give you the right answer. *
Real life Example
Bank loan dashboard
Imagine you work at XYZ bank as a Business Analyst.You are required to provide a report showing loan performance. You have three tables.
1.Loans table.
2.Customers table.
3.Branches Table.
You connect them like this.
Customer ID in the loans table connects to customer ID in the customer's table.
Branch ID in the loans table connects to branch ID in the branches table.
When a manager clicks on Kakamega branch, Power BI automatically filters the loan table to show only Kakamega branch loans.
So before you drag another visual onto that canvas, check your data model.
LET'S TALK JOINS
Joins-Before your data lands in Power BI, sometimes you need to combine tables first. This is where joins come in. A join is simply a way of saying ''take this table and that table and bring them together based on a matching column''
Think of it like this. You have a class register with student names and IDs. You have a marks sheet with student IDs and scores. A join connects the two so you can see each student's name alongside their score in one clean table.
There are four main types of joins.
Inner join — give me only the rows that match in both tables. If a student is in the register but not the marks sheet, they are left out completely.
Left join— give me everything from the left table, and match what you can from the right table. If there is no match, leave the right side empty. This is the most common join in data work.
Right join — the opposite of a left join. Keep everything from the right table and match what you can from the left.
Full outer join — give me everything from both tables regardless of whether there is a match. Unmatched rows get blanks on the side that has no data.
SEE BELOW DIAGRAM
What is the most challenging part of data modelling for you?
Top comments (0)