DEV Community: Joan

Power BI Data Modeling: Schemas, Relationships, Joins, and Why They All Matter explanations

Joan — Wed, 24 Jun 2026 13:45:00 +0000

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.

How Excel Is Actually Used in Real-World Data Analysis

Joan — Sat, 06 Jun 2026 06:21:46 +0000

I’ll be honest, when I first heard “Excel for data analysis,” I mentally filed it under things I already know. I’ve spent a few years in tech, pre-sales, project management, and vendor partnerships, and I figured Excel was just spreadsheets with a few formulas. I was wrong.

This week in my Data Science & Analytics course at LuxDev HQ, we worked on an HR dataset. And it was messy. Missing hire dates, duplicate employees, an age listed as 99 and someone’s project count written as “ten” instead of 10. Real data. Real chaos.

Working through that dataset taught me more about data than any definition ever could. Here’s what I’ve learned about how Excel is really used in practice.

But first, what is Excel?

Excel is a spreadsheet tool from Microsoft for storing, organising, and analysing data in rows and columns. Think of it as a super-powered table one where you can run calculations, spot patterns, and clean up messy information, all in one place.

3 Ways Excel Shows Up in the Real World

HR & People Data Management
HR teams use Excel to track headcount, flag performance gaps, and plan promotions. Our dataset spanned 80+ employees, multiple departments, and locations from Nairobi to New York. Excel is what kept the chaos in check and made the analysis possible

Financial Reporting & Payroll
Before any payroll report goes out, someone has to total the right figures, check for missing entries, and make sure everything adds up. That work happens in Excel, using the exact formulas we practiced in class.

Business Decision-Making
When a manager asks “How many permanent staff are in Nairobi?” or “What’s the total bonus payout for Sales?”—those are business questions that Excel answers in seconds, with the right formula.

3 Excel Features I Learned This Week

Conditional Formatting

This highlights cells automatically based on rules you set. I used it to spot outliers—an age of 99, a last promotion year of 1900, and a bonus with a currency symbol. Instead of scanning hundreds of rows, Excel flagged the problems instantly.

ROUND, ROUNDUP
These functions turn rough numbers into professional reporting

SUMIF, SUMIFS, COUNTBLANK, COUNTIF, COUNTIFS — Aggregate Functions with Conditions

This is where Excel starts to feel like actual analysis. These functions let you ask specific questions of your data, not just “what’s the total” but “what’s the total for this specific group, under these specific conditions.”
COUNTBLANK** This counts every empty cell, so before I even started cleaning, I knew exactly how many gaps I was dealing with.

COUNTIF lets me count how many employees belonged to each department, or how many had a performance score of 8 or above. One formula, one condition.
COUNTIFS** took it further, two or more conditions at once. How many employees are Interns, female, above 40 yrs AND based in Nairobi? How many are in IT AND fully remote?

SUMIF gave me the total bonus paid out to the Sales department. =SUMIF(D:D, "Sales", K: K) that’s it. One formula.

SUMIFS added another layer, total bonuses for Permanent staff in the Sales department only. Multiple columns, multiple conditions, one clean result. This is what financial summaries are actually built from.

How This Week Changed the Way I See Data

Before this week, I saw data as something you simply report. Now, I realize data is something you interrogate, challenge, and understand before you trust it. Every inconsistency is a clue to a human story or a broken process. It’s the analyst’s job to find, fix, and ensure the data is trustworthy before anyone makes a decision based on it.

If you’re just getting into data analytics, start with Excel. It will teach you how to think about data long before you write your first line of code.