Here’s what I’ve learnt about Power BI: creating visuals is the easy bit. What truly determines your dashboard’s success happens long before you drag your first chart onto the canvas; it’s all about how you design your data model.
If your model isn’t set up correctly, your numbers can be inaccurate in ways that are tough to diagnose. Let’s break it down from the ground up so we can get it right from the start.
What Is Data Modeling?
Data modeling means organizing your tables so Power BI understands how they connect. Before you build any visuals, it’s essential to define which tables relate to each other, how they’re linked, and the direction filters should flow.
Think of it like organising your desk before you start working. If everything is in the right place, the work goes smoothly. If it is not, you spend the whole session looking for things.
A good model is accurate and easy to maintain. A poor one leads to confusing errors. In Power BI, you’ll mainly use two table layouts:
- -Star Schema
This is Power BI’s standard. Puts your main fact table in the center, with dimension tables around it (shaped like a star).
Power BI works best with a star schema—filters and calculations are simple, and performance is fast. When unsure, use this structure.
- Snowflake Schema
A snowflake schema takes the star schema and breaks down dimension tables further into sub-tables. This adds complexity and usually slows things down in Power BI.
If your source uses a snowflake schema, try to flatten it into a star schema in Power BI before modeling.
Relationships — How Tables Talk to Each Other
A relationship is the link between two tables, defined by a shared column, usually an ID field. In Power BI, you set these up in the Model view, and there are three things to understand about every relationship you create:
1.Cardinality — the ratio between the two tables
-One-to-Many (most common): one row in Table A matches many rows in Table B. One department, many employees. One product, many sales records.
-One-to-One: rare in practice. If two tables have a 1:1 relationship, you can usually just merge them.
-Many-to-Many: Power BI supports it, but it needs deliberate handling. Used carelessly, it produces duplicated or inflated results
2.Cross-filter Direction — which way filters flow
-Single: filters flow one way only, from the dimension table to the fact table. This is the default and the safest option.
- Both: filters flow in both directions. Useful with Many-to-Many or role-playing dimensions, but can cause filter ambiguity in complex models. Use it intentionally.
3.Active vs Inactive Relationships
Only one relationship between two tables can be active at a time. In the Model view, active relationships appear as solid lines, while inactive relationships are shown as dashed lines. You can double-click any relationship line to open the Edit Relationship dialog and toggle the Active checkbox directly, making it the permanent default. Alternatively, keep it inactive and call it on demand in DAX using USERELATIONSHIP()
.
Joins — Merging Tables in Power Query
Joins happen in Power Query. When you use Merge Queries, you are combining two tables based on a shared column. The join type you choose determines which rows survive the merge.
Here is every join type in Power Query, and when to actually use it:
In practice, Left Outer is the one you will reach for most often. Keep everything from your main table, bring in what matches from the lookup table, and handle any nulls from there.
How It All Fits Together — A Real Example
Say you are building an HR dashboard from a dataset, with employee records, salaries, bonuses, and departments.
•Your fact table is the employee records — salaries, bonuses, performance scores, and project counts.
•Your dimension tables are Department, Location, and Employee Type.
•You build a star schema with the employee table in the centre.
•Power BI creates one-to-many relationships between each dimension and the fact table.
•If you needed to bring in a separate leave records table, you would use a Left Outer join in Power Query to merge it in before loading.
Now, when someone slices by department or filters by location, Power BI knows exactly how to follow those relationships and return the right numbers every time.
The Takeaway
Many of us start creating visuals in Power BI and later question why our totals do not add up or why the slicers behave unexpectedly. The solution is often found within the model.
Get the schema right. Define your relationships carefully. Understand your joins before you load. The visuals will take care of themselves.
Top comments (1)
Great job 👍