Power BI Relationships & Joins: Why Your Tables Need to Be On Speaking Terms

Felix Ng'ang'a — Fri, 03 Jul 2026 12:22:54 +0000

Power BI Relationships & Joins: Why Your Tables Need to Be On Speaking Terms

Picture this: you walk into a wedding. The seating chart says Table 7. Your name card is there, but somehow it's sitting next to people you've never met, at a table meant for the groom's college friends, while your actual friends are three tables away wondering where you went.

That's what a Power BI report looks like when your relationships are wrong. The data isn't lying to you it's just sitting at the wrong table.

If you've ever built a report where the numbers looked almost right, or a slicer that refused to filter a visual no matter how hard you clicked it, you've met this problem before. Let's actually fix it, and understand why it happens.

Your Tables Are Strangers Until You Introduce Them

When you load data into Power BI from a sales system, a CRM, and a spreadsheet someone's been maintaining since 2019, you don't get one big blob of information. You get several separate tables, like guests arriving from different families who've never met.

Sales — knows what got sold, when, and how much
Products — knows what the products actually are
Customers — knows who bought them
Calendar — knows what a "quarter" even means

Power BI doesn't automatically know that the ProductID in your Sales table refers to the same product as the ProductID in your Products table. You have to introduce them. That introduction is a relationship.

Think of a relationship like exchanging phone numbers at that wedding. Once two tables have each other's number, they can pass messages back and forth specifically, filter context. Click on "Nairobi" in a Customers slicer, and if the relationship exists, Sales immediately knows to only show orders from Nairobi customers. No relationship, no phone number, no message gets through. The slicer clicks, nothing moves, and you're left refreshing the page wondering if the report is broken.

The Star Schema: Sitting Everyone at the Right Table

Here's where the wedding analogy earns its keep. A well-run wedding doesn't seat every guest randomly there's a top table (the couple) and everyone else is arranged around it, each guest at exactly one table, with a clear line back to the center.

That's a star schema. One central table usually your Sales or Transactions table, packed with numbers you want to measure (revenue, quantity, discount) surrounded by smaller tables that describe who, what, where, and when. Draw it out and it genuinely looks like a star, with Sales at the middle and Customers, Products, Calendar, and Stores radiating outward.

The alternative a snowflake schema is what happens when your seating chart gets fancy: Products connects to Subcategory, which connects to Category, which connects to Department. Technically organized, but now a simple question like "what's our furniture revenue?" has to travel through three tables to get an answer. It works, but it's slower to reason about and slower for Power BI's engine to chew through.

Most of the time, star beats snowflake. Simpler paths, faster performance, and importantly you can actually explain it to your manager without a whiteboard meltdown.

One-to-Many: The Manager and the Team

The most common relationship you'll build is one-to-many. Think of it like one manager overseeing many employees. In Customers, "Amina" appears exactly once she's a single row, a single person. In Sales, "Amina" might appear fifty times, once for every order she's ever placed.

That's the shape: one row in Customers connects to many rows in Sales. This is by far the most common relationship in Power BI, and it's the one the star schema is built on. Products has one row per product; Sales has many rows referencing that product. Calendar has one row per date; Sales has many transactions on that date.

The direction of the arrow matters too filters generally flow from the "one" side to the "many" side. Amina's customer record can filter her orders.

Many-to-Many: When Everyone Knows Everyone

Sometimes there's no single "one" side. Imagine a group of friends carpooling to that wedding several people can ride in several different cars, and no car belongs to just one person. That's many-to-many.

In Power BI, this shows up when, say, a Promotions table lists discount codes, and a single sale can involve multiple promotions, while a single promotion can apply to multiple sales. Neither side is uniquely one thing. Power BI can handle this, but it's the seating arrangement most likely to cause a headache ambiguous filtering, duplicated values, numbers that look inflated. Use it when you genuinely need it, but don't reach for it as your default; it's the wedding-planning equivalent of "let's just let everyone sit wherever."

Joins: How the Introduction Actually Happens

If a relationship is the phone-number exchange, a join is the actual conversation the moment Power BI's engine physically combines rows from two tables based on a matching column, usually during Power Query transformations rather than the report canvas itself.

Say you're merging a Sales table with a Returns table. You've got choices for how to handle guests who show up on one list but not the other:

Inner Join — only the sales that also appear in returns. Only guests whose names appear on both the bride's list and the groom's list get a seat. Strict, but sometimes too strict — you lose everyone else.
Left Outer Join — every sale, plus matching return info where it exists. Every guest the bride invited gets a seat; if they also happen to be on the groom's list, great, extra details get attached, but nobody from her list gets left out.
Right Outer Join — the mirror image: every return, plus matching sales where they exist.
Full Outer Join — everyone from both lists, matched where possible, with blanks where there's no match on the other side. Nobody gets turned away, even if some details are missing.
Anti Joins (left/right) — the interesting one for troubleshooting: show me sales that have no matching return. This is how you'd find, say, every guest who RSVP'd but was mysteriously left off the actual seating chart great for finding orphaned records, orders with no customer match, or products that were sold but never appear in your master product list.

In Power Query, you'll do this through the Merge Queries dialog, picking your two tables, the matching column, and the join type from a dropdown. It looks unassuming for something that decides whether your final row count is 10,000 or 47.

Why This All Actually Matters

Here's the part that turns this from trivia into something worth caring about: a report can be visually perfect clean colors, tidy cards, a nice looking donut chart and still be quietly wrong underneath, because a relationship was set to many-to-many by accident, or a join duplicated every row three times, or a filter direction was pointing the wrong way.

Nobody claps when your relationships are correct. But everybody notices when the total revenue on your dashboard doesn't match the total revenue in the CFO's spreadsheet. That mismatch is almost never a "Power BI bug." It's a seating chart problem.

The Takeaway

Relationships tell your tables who's related to whom. Joins are the mechanics of actually stitching that data together. Get the shape right star schema where you can, one-to-many as your default, many-to-many only when you truly need it and the rest of your report, the DAX measures, the slicers, the drill throughs, all of it starts behaving the way you'd expect.

Next time a visual in your report looks off, don't start by rewriting your measure. Go check the seating chart first.

If you've run into a Power BI relationship horror story duplicated rows, phantom totals, a many-to-many you didn't mean to create drop it in the comments. Misery loves company, and so does debugging.

EXCEL IN DATA ANALYSIS

Felix Ng'ang'a — Sat, 06 Jun 2026 14:56:01 +0000

Excel is a Microsoft application program which incorporates spreadsheets to store, organize, manipulate, and analyze data. It has workbooks and worksheets, whereby in each workbook you have multiple pages called worksheets. On the worksheet there is a grid which contains rows, columns, and cells. The columns run vertically and are labeled with letters. Rows run horizontally and are labeled with numbers. Cells are individual boxes where rows and columns intersect, and each cell has a unique address.

Excel can be categorized into three pillars: data entry and organization, calculations, and visualization and analysis. In data entry, cells can hold information like text, numbers, dates, and percentages. It allows you to make this data readable. For example, functions and formulas: a function is a predefined calculation like SUM or AVERAGE that takes input values, whereas a formula is a complete expression that uses functions, references, and operators to perform calculations in a cell. Excel helps one interpret data by visualizing it into forms of charts and tables.

In a data driven world, Excel is a powerful tool for real world data analysis across various industries and institutions. Financial institutions such as banks or investment firms use Excel for financial reporting, risk assessment, and portfolio valuation by integrating market data with investment metrics. Marketing strategists use Excel to measure campaign performance, customer trends, and return on investment. Even in smaller businesses, Excel helps track inventory, sales, and employee hours.

Out of all the powerful features I have learned so far, Excel's best functions are conditional functions such as AVERAGEIF and COUNTIF. I realized this when my instructor gave us a dataset and asked two specific questions. First: "What is the average feedback score for Single Males?" Second: "What's the total bonus for employees earning above 100,000?" At first, I thought I would have to manually sort and filter, which would have taken forever. Then I used AVERAGEIF to calculate the average feedback score for only the rows where marital status was "Single" and gender was "Male." For the second question, I used SUMIF to add up bonuses only for employees whose salary was above 100,000. Within seconds, I had both answers. That was the moment I truly saw the potential of Excel. It wasn't just about typing in data anymore it was about asking the right questions and letting the functions do the heavy lifting.

In conclusion, Excel has made me see the true potential of extracting information from data. Before that assignment, I thought Excel was just for making simple tables and basic addition. But after using AVERAGEIF and SUMIF on a real dataset, I understood what my instructor meant when he told us that data gives an opinion. You just have to know how to ask the right question. It has shown me the true value of data and what it can say when you know how to listen.

DEV Community: Felix Ng'ang'a

Power BI Relationships & Joins: Why Your Tables Need to Be On Speaking Terms