Introduction
Imagine you're a farmer in Kiambu, Kenya. You keep one notebook listing all the farmers in your cooperative, another notebook recording every harvest how much was planted, how much was yielded, and the profit made and a third notebook listing the different crop types and varieties grown in the region. Each notebook makes sense on its own, but the real value comes when you can flip between them and answer a question like: "Which county produced the most profitable potato harvest last season?"
That's exactly the problem Power BI solves with relationships and joins. This article uses a real dataset of over 300 Kenyan crop harvest records covering counties, farmers, crop types, seasons, and profits. To walk through what these terms mean, why they matter, and how to set them up. No coding background required.
What Is a "Relationship Schema"?
A schema is simply a map of your data it shows what tables you have and how they connect. Think of it like a family tree, but for spreadsheets.
Our original dataset arrived as one flat spreadsheet, with 313 rows and 19 columns every harvest record repeating the farmer's name, county, crop type, and contact number over and over again. That's fine for a small file, but it makes analysis clumsy and error-prone: if "Kiambu" is spelled two different ways anywhere in those 313 rows, Power BI will treat them as two different counties.
The fix is to split that single flat file into separate, connected tables a relationship schema. For this dataset, that means:
- Fact_Harvest the core transactional table: one row per harvest, holding measurements like yield, revenue, cost, and profit.
- Dim_County one row per Kenyan county (Kiambu, Meru, Nakuru, and so on).
- Dim_Farmer one row per farmer, with their name and contact number.
- Dim_Crop one row per crop type and variety combination (e.g., Potatoes Organic).
- Dim_Season one row per growing season (Long Rains, Short Rains, etc.).
The Two Common Schema Shapes
Star Schema
Picture one central table Fact_Harvest with the smaller reference tables branching out from it like the points of a star: Dim_County, Dim_Farmer, Dim_Crop, and Dim_Season. Each "point" table connects directly to the central fact table through an ID column.
This is the layout used below, and it's the one Power BI performs best with.
Power BI Model view showing the star schema built from this dataset
Power BI Model view showing Fact_Harvest connected to Dim_County, Dim_Farmer, Dim_Croptype, and Dim_Season
Snowflake Schema
This is a star schema broken into smaller pieces. For example, Dim_Crop could itself be split further into a separate Dim_CropCategory table. It's more normalized but adds complexity best avoided unless there's a clear need.
What Are "Joins" (Relationships) in Power BI?
In everyday database language, these connections are often called "joins." In Power BI specifically, they're called relationships, and you build them visually no code required.
A relationship links a column in one table to a matching column in another table. In our dataset: the CountyID column in Fact_Harvest links to the CountyID column in Dim_County. The FarmerID in Fact_Harvest links to FarmerID in Dim_Farmer. And so on for CropID and SeasonID.
Types of Relationships
This sounds technical, but it just describes how many matching records exist on each side.
- One-to-Many (most common): One county (Kiambu) can appear against many harvest records in Fact_Harvest, but each harvest record belongs to only one county. This is exactly the relationship between Dim_County and Fact_Harvest in our model.
- One-to-One: Each record in Table A matches exactly one record in Table B (rare not present in this dataset).
- Many-to-Many: Records on both sides can repeat. Power BI supports this, but it should be used carefully since it can make totals harder to trust.
The "Edit relationship" dialog in Power BI, showing the relationship between Fact_Harvest and Dim_County
Power BI Edit relationship dialog showing Fact_Harvest joined to Dim_County on CountyID, cardinality Many to one
Notice the cardinality is set to "Many to one (*:1)" many harvest records point to one county and the cross-filter direction is "Single," meaning filtering by county correctly filters the harvest records, not the other way around.
Cross-Filter Direction
This determines which table "controls" the filtering of the other. In our model, filtering a report by County (say, selecting only "Meru") will correctly filter Fact_Harvest down to only Meru's harvests. Power BI sets this automatically for star schemas, so as a beginner you rarely need to change it.
How to Create a Relationship in Power BI
- Load the split tables into Power BI Desktop:
Fact_Harvest.csv,Dim_County.csv,Dim_Farmer.csv,Dim_Crop.csv, andDim_Season.csv. - Click the Model view icon on the left-hand sidebar (it looks like three connected boxes).
- You'll see each table laid out as a card with its column names listed inside.
- Click and drag
CountyIDfrom Fact_Harvest directly ontoCountyIDin Dim_County. - Power BI draws a line between them and automatically detects the relationship type.
- Double-click that line to open Edit relationship, where you can confirm the cardinality and filter direction, exactly as shown in Screenshot 7 above.
- Repeat for
FarmerID,CropID, andSeasonID.
Why Does This Matter?
If CountyID in Fact_Harvest isn't properly connected to Dim_County, Power BI has no way of knowing that "CountyID 3" refers to Kiambu. This leads to:
- Profit totals that don't split correctly by county in a chart
- Slicers for crop type or season that don't filter anything
- Reports that look fine but are quietly giving wrong answers
Setting up a clean star schema Fact_Harvest at the center, with Dim_County, Dim_Farmer, Dim_Crop, and Dim_Season branching out is like organizing farm notebooks so that when you ask "which county had the highest average profit per acre this season," every notebook can be consulted together and give one accurate, trustworthy answer.
Conclusion
Relationships and schemas might sound like advanced database jargon, but at their core they're about connecting related pieces of information so they can talk to each other. Using this real dataset of 500 Kenyan crop harvests, we went from one unwieldy flat file to a clean star schema and once that connection is in place, questions like "which crop is most profitable in the Short Rains season" become a simple drag-and-drop report instead of a manual spreadsheet headache.
Top comments (1)
Good work, and the analogy really did hit home.