How I Modelled My Power BI Data - Data Modelling, Relationships & Joins (Kenya Crops Dataset)

Introduction Power BI is Microsoft's tool for turning raw data , an Excel file, a database, into interactive dashboards and reports, without needing to write much code. You connect a data source, clean it up, build relationships between tables, and then drag fields onto charts that anyone can click through and filter. What makes it powerful, from what I've seen building this project, is that it isn't just a chart-maker. It handles genuinely large datasets without any struggles , it can refresh itself automatically once published to the cloud, and DAX (a formula language) ,lets you build calculations far beyond what a spreadsheet can do ,year-over-year comparisons, running totals, conditional logic, all recalculating instantly as someone filters the report. And because it's free to start with (Power BI Desktop costs nothing), the barrier to trying it is basically zero. For a dataset like mine , a few hundred farm records ,it's overkill in raw power, but that's exactly why it scales so well once a project grows past a class assignment. Data Modeling Before I built a single chart, I had to decide how my tables should be organized . That decision ,data modelling is easy to skip past, but it quietly controls four things later: How fast my reports load - a messy model makes Power BI do more work for every click. How easy my formulas are to write - a clean model means shorter, simpler DAX. How well the model grows - can I add a new county or crop next season without breaking everything? How easy it is for someone else to follow - a teammate (or future me) should be able to open my file and understand it in minutes. So before touching visuals, I worked through the three common ways to organize tables in Power BI: a flat table, a star schema, and a snowflake schema - and looked at which one actually fits my project. 1.The Flat Table A flat table is the simplest possible design: everything lives in one wide table. Every fact (like revenue or yield) sits in the same row as every descriptive detail (like the farmer's name, the county, the crop type, the season). This is exactly how I built my Kenya Crops Power BI file , one single table called Kenya Crops Data set , with columns for the farmer, county, crop type, season, soil type, dates, yield, revenue, cost, and profit, all in the same table. What I like about it It's very easy to follow ,what I see is what I get. No relationships to set up. Nothing to link. It was the fastest way to get my data in and start building measures and visuals early on. What bothers me about it There's a lot of repetition. If "Kericho" is the county for 500 of my records, the word "Kericho" is physically stored 500 times instead of once. As I add more rows, the file gets heavier and slower to work with. If I ever needed to correct a county's spelling, I'd risk missing some of the rows it appears in. I can't easily reuse my "County" or "Crop Type" list in another table if I add one later. Why I still went with it for now For a project this size ,a single class dataset , a flat table was genuinely a reasonable starting point, which is why I built it this way first. I don't think it would hold up well if I connected more data sources or scaled this into a real reporting tool, but for getting the measures and visuals working, it did the job. On performance: I noticed flat tables are simple to build but get slower to use as the data grows, because DAX has fewer shortcuts to lean on when everything sits in one giant table. 2. The Star Schema (what I'd redesign it into) A star schema splits the same information into two kinds of tables: One central table of events (a fact table) -in my case, one row per farm's crop record: yield, revenue, cost, profit. Several smaller tables around it that describe things about those events (dimension tables) ; one table for farmers, one for crop types, one for counties, one for dates. Each dimension table connects straight to the fact table, like the points of a star. (See Diagram 2 below for how I'd redesign my own data this way.) What I like about this design No repeated text ;"Kericho" would be stored once in DimCounty, and every fact row just points to it. It's fast. Power BI and DAX are built to work brilliantly with this shape. Filtering becomes simple ,dragging "County" onto a chart automatically filters my fact table correctly. Anyone looking at the model diagram could immediately understand what connects to what. The trade-off It's more setup work upfront ;I'd have to split my one flat table into several related tables, which takes planning. A little duplication within a dimension table is normal and fine (that's the trade-off for speed). Why I'd choose this if I rebuilt the project If I were taking this dataset further , this is the design I'd commit to. It's the standard choice for a proper Power BI report. 3. The Snowflake Schema A snowflake schema takes a star schema one step further: one or more dimension tables gets split again into smaller, more detailed tables. For my data, I could split my DimCounty table further ;pulling "Region" out into its own DimRegion table connected to DimCounty. (See Diagram 3 below.) *What it would give me * Even less repeated data ; a region name stored once, not once per county. It would make sense if "Region" was a big, shared list reused across several fact tables. Why I'm not going this route Power BI has to "hop" through an extra table to answer some questions, which adds a bit of complexity and can slow things down slightly. It's more tables for me to manage and a busier model diagram, for a benefit I don't really need on a project this size. My take: for my Kenya Crops project specifically, I decided snowflaking isn't worth it ; my dimensions (farmer, crop, county, date) aren't big or reused enough to justify the extra hops. Quick comparison , how I'd weigh my three options Flat Table (what I have)|Star Schema (what I'd move to) |Snowflake Schema Setup effort Lowest Medium Highest Report speed Slows down as I add data Fast Fast, with tiny extra hops Data repetition High Low Lowest Easiest for me to read Yes, for now Yes, overall Gets busy fast Best fit for my project Early exploration If I scale this up Not needed here 4. Fact Tables vs Dimension Tables, in my own data Once I split away from my flat table, everything in my model would fall into two roles: A fact table stores the numbers that happened ; the measurable events. In my dataset that's Yield (Kg), Revenue (KES), Cost of Production (KES), and Profit (KES). Common naming elsewhere: FactSales, FactOrders, FactTransactions ;mine would be FactCropProduction. A dimension table stores the descriptions ;who, what, where, when ;the things I'd use to slice, filter, or label a report. Mine would be DimFarmer, DimCropType, DimCounty, DimDate. The way I remember it: numbers I add up belong in the fact table; words I filter by belong in dimension tables. One more idea I had to get straight for myself: grain. The grain of my fact table is the answer to "what does one row actually represent?" For my data, that's one farmer's one crop record for one season ,not one farmer, and not one county on its own, but that specific combination. Every measure I write assumes that grain, so I made sure I understood it before writing any DAX. The practical picture of my fact table connected to its dimension tables is the same star layout as Diagram 2 below. 5. Relationships - how I'd connect my tables A relationship is a link between two tables that tells Power BI "these two are connected, and here's how." I'd need relationships the moment I split my flat table into a star schema, so Power BI knows how to bring everything back together for a report. There are three kinds, based on cardinality - how many matching rows sit on each side. (See Diagram 4 below for how I'd map each type onto my own tables.) *One-to-Many (1:) *- the one I'd use almost everywhere. One row in a dimension table matches many rows in my fact table. In my case: one crop type ("Maize") would appear on many of my farm records. One-to-One (1:1) * rare, and I don't actually need it in this project, but I'd use it only if I ever split off a DimFarmerContact table from DimFarmer, where each farmer has exactly one contact record. Many-to-Many (:) I'd avoid this unless I truly needed it. An example from my own domain: if a farm could hold several certifications, and a certification could apply to several farms, that relationship would need to be many-to-many. I don't have this in my current data, so I wouldn't add it just to have it. Why Primary Keys, Foreign Keys, and uniqueness mattered to me here A Primary Key (PK) -is the column that uniquely identifies each row in a dimension table - no duplicates. In my DimCounty, CountyID would be unique: each county appears exactly once. ** A foreign key is the matching column in my fact table that points back to that primary key - and it's expected to repeat. CountyID would show up once for every single farm record in that county. This is exactly why, in my data, CountyID would be unique in the dimension table but appear many times as a foreign key in the fact table - that repetition is what makes the "many" side of my one-to-many relationship work. Referential integrity means every foreign key in my fact table actually has a matching row in the dimension table - I wouldn't want a farm record pointing at a county that doesn't exist anywhere in DimCounty. Active vs inactive relationships: Power BI only lets one relationship between two tables be "active" at a time. If I needed a second connection - say, filtering by both Planting Date and Harvest Date against the same date table - the second one would be created inactive, and I'd only switch it on inside a specific formula when I needed it. 6. Filter Direction - the part I had to slow down and think about Once my tables are connected, Power BI needs to know which way a filter is allowed to travel through that connection. (See