DEV Community: Kendi

From Straight Lines to Smarter Models: Understanding Regression and Regularization in Machine Learning

Kendi — Tue, 21 Jul 2026 11:55:03 +0000

Part 1: Linear Regression — What the Model Actually Does

Linear regression is one of the simplest predictive models in machine learning. Its job is straightforward: learn the relationship between a set of input features and a continuous target by fitting the best possible line through the data. If you have many features, that "line" becomes a hyperplane, but the underlying idea stays the same.

For multiple features, the model can be written as:

ŷ = β₀ + β₁x₁ + β₂x₂ + ... + βₙxₙ

Where ŷ is the predicted value, β₀ is the intercept, and β₁ through βₙ are coefficients that quantify how much each feature contributes to the prediction.

How Coefficients Are Found: Ordinary Least Squares

The model finds the coefficients that minimise the Residual Sum of Squares (RSS):

RSS = Σ(yᵢ - ŷᵢ)²

Each term (yᵢ - ŷᵢ) is the residual — the difference between the actual value and the model's prediction for observation i. Squaring serves two purposes: it prevents positive and negative errors from cancelling each other out, and it penalises large errors more heavily than small ones.

This approach is called Ordinary Least Squares (OLS). It has a closed-form analytical solution, meaning the optimal coefficients can be calculated directly rather than estimated iteratively. This makes OLS computationally efficient and, importantly, it produces the Best Linear Unbiased Estimator (BLUE) when the five classical regression assumptions hold.

The Five Assumptions

Linearity. The relationship between predictors and target must be approximately linear. A model cannot capture a curve by drawing a straight line.

Independence of errors. Residuals for different observations should not be correlated. This assumption is most frequently violated in time series data, where prediction errors in one period predict errors in the next.

No multicollinearity. Predictor variables should not be highly correlated with each other. When they are, the model cannot reliably separate their individual effects. Coefficients become unstable — small changes in the data produce wildly different estimates.

No influential outliers. A single extreme observation can pull the regression line toward it, distorting coefficients for all other rows. The model minimises squared errors, so large errors have disproportionate influence.

Normality of residuals. Errors should be approximately normally distributed. This matters primarily for hypothesis testing on coefficients — calculating p-values and confidence intervals. It is less critical for prediction.

Detecting Multicollinearity: VIF

The Variance Inflation Factor (VIF) quantifies how much the variance of a coefficient is inflated due to multicollinearity:

VIF = 1 / (1 - R²ⱼ)

Where R²ⱼ is the R² obtained when predicting feature j from all other features. If feature j can be predicted well from its peers, it is nearly redundant, and VIF will be high. A VIF above 10 is a serious signal; above 5 warrants attention.

from statsmodels.stats.outliers_influence import variance_inflation_factor

X_with_constant = X_numeric.copy()
X_with_constant['constant'] = 1

vif_data["VIF"] = [variance_inflation_factor(X_with_constant.values, i)
                   for i in range(len(X_numeric.columns))]

Part 2: Evaluating Model Performance

Training a model is only half the work. The other half is understanding how well it performs — and, critically, whether that performance will hold on data the model has never seen.

The Four Core Metrics

Mean Absolute Error (MAE) is the average of the absolute prediction errors. It is easy to interpret — an MAE of 5,000 means predictions are off by approximately 5,000 units on average. It treats all errors equally.

Mean Squared Error (MSE) squares the errors before averaging. This penalises large errors disproportionately. The units are squared, making direct interpretation difficult.

Root Mean Squared Error (RMSE) is the square root of MSE, returning the metric to the original units. RMSE will always be equal to or larger than MAE. When the gap is large, outlier predictions exist. RMSE is appropriate when large errors are especially costly.

R² (Coefficient of Determination) measures the proportion of variance in the target variable explained by the model:

R² = 1 - (SS_residual / SS_total)

An R² of 0.96 means the model explains 96% of the variation in the target. The remaining 4% is attributable to factors outside the model or genuine randomness.

A critical limitation of R²: adding any predictor — even a completely irrelevant one — will never decrease R². A model with shoe size as a feature will have equal or higher R² than one without it. This makes R² unreliable for comparing models with different numbers of features.

Adjusted R² corrects for this by applying a penalty for adding predictors that do not genuinely improve the model. It decreases when an irrelevant predictor is added and increases when a useful one is. Use Adjusted R² whenever comparing models with different feature counts.

The Train-Test Split

Performance metrics calculated on training data are meaningless as evaluation tools — the model has already seen that data and optimised itself against it. True evaluation requires testing on data the model has never encountered.

The standard approach is to hold out a portion of data before training:

from sklearn.model_selection import train_test_split

X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

test_size=0.2 reserves 20% for testing. random_state=42 makes the split reproducible. You train on X_train and y_train, then evaluate predictions on X_test against y_test — data the model never touched during training.

Part 3: The Overfitting Problem

On a salary prediction project using the job_salary_prediction_dataset, my plain linear regression model produced:

Training R²: 0.9634
Test R²: 0.9635
Gap: 0.0001

This is a well-behaved model. The near-identical scores on training and test data indicate the model learned genuine patterns rather than memorising the training data's noise.

But this result is not always guaranteed. The gap between training and test performance tells the real story.

Overfitting Defined

Overfitting occurs when a model learns the training data too specifically — including its noise, outliers, and dataset-specific quirks — and fails to generalise to new observations. It manifests as high training performance and substantially lower test performance.

At the coefficient level, overfitting produces very large values. The model assigns extreme weights to certain features to minimise training error, at the expense of generalisation.

Underfitting is the opposite failure: the model is too simple to capture the underlying pattern and performs poorly on both training and test data.

The tension between the two is formalised as the bias-variance tradeoff:

High bias (underfitting): the model consistently misses the true pattern. Rigid, makes systematic errors.
High variance (overfitting): the model is too sensitive to the specific training data. Small changes in training data produce large changes in predictions.

Reducing bias typically increases variance, and vice versa. The goal is to find the model complexity that balances both.

Part 4: Regularization — Adding a Penalty

Regularization addresses overfitting by modifying the cost function. Instead of minimising only the prediction error, the model simultaneously minimises prediction error and a penalty on the size of the coefficients. Large coefficients are discouraged, producing a simpler, more generalisable model.

Three regularization techniques are widely used in practice.

Lasso Regression (L1)

Cost = RSS + α × Σ|βⱼ|

Lasso adds a penalty proportional to the sum of absolute values of the coefficients. The parameter α controls the strength of the penalty: α = 0 reduces to plain linear regression, and increasing α progressively shrinks coefficients.

The critical property of Lasso — a consequence of the geometry of the L1 penalty — is that it can push coefficients to exactly zero. When a coefficient reaches zero, that feature is removed from the model entirely. Lasso performs automatic feature selection.

This makes Lasso valuable in high-dimensional settings where many features are suspected to be irrelevant. In the salary prediction project, running Lasso with α = 1 zeroed out industry_Education, identifying it as a non-contributor to salary prediction. Increasing α to 1000 zeroed out 23 features — but at the cost of dropping the R² from 0.9635 to 0.7719. This is the bias-variance tradeoff made visible: the stronger the penalty, the simpler the model, and beyond a point, too simple to capture genuine patterns.

from sklearn.linear_model import Lasso

lasso = Lasso(alpha=1)
lasso.fit(X_train_encoded, y_train)
lasso_pred = lasso.predict(X_test_encoded)

Use Lasso when: you suspect many features are irrelevant and want the model to identify and remove them automatically.

Limitation: when two features are highly correlated, Lasso tends to arbitrarily select one and zero out the other, which may not reflect the true underlying structure.

Ridge Regression (L2)

Cost = RSS + α × Σβⱼ²

Ridge uses the sum of squared coefficients as its penalty. The structure is nearly identical to Lasso, but the geometry of squaring means Ridge shrinks coefficients close to zero without ever reaching it exactly. Every feature remains in the model — only with reduced influence.

This behaviour makes Ridge more appropriate when all features are expected to contribute something meaningful, and particularly when predictors are correlated. Ridge distributes the weight between correlated features more gracefully than Lasso's winner-take-all approach.

from sklearn.linear_model import Ridge

ridge = Ridge(alpha=1)
ridge.fit(X_train_encoded, y_train)
ridge_pred = ridge.predict(X_test_encoded)

Use Ridge when: all features are genuinely relevant, or when multicollinearity between predictors is a concern.

Elastic Net (L1 + L2)

Cost = RSS + λ[α × Σ|βⱼ| + ((1-α)/2) × Σβⱼ²]

Elastic Net combines both penalties. The parameter l1_ratio (α in the formula above) controls the blend:

l1_ratio = 1.0 → pure Lasso
l1_ratio = 0.0 → pure Ridge
l1_ratio = 0.5 → equal mix

This flexibility resolves the individual weaknesses of each: Elastic Net can zero out genuinely irrelevant features while handling correlated features more gracefully than Lasso alone.

from sklearn.linear_model import ElasticNet

elastic = ElasticNet(alpha=0.1, l1_ratio=0.5)
elastic.fit(X_train_encoded, y_train)

Use Elastic Net when: you are uncertain whether Lasso or Ridge is more appropriate. It is a reliable default in high-dimensional problems.

Part 5: When to Use Each Approach

The choice of regularization is not arbitrary — it should follow from what you know about your data and the problem structure.

Technique	Penalty	Zeros coefficients?	Best suited for
Plain OLS	None	No	No overfitting present
Lasso (L1)	Absolute values	Yes	Many irrelevant features
Ridge (L2)	Squared values	No	All features relevant; high correlation
Elastic Net	Both	Yes	Uncertain — safe default

A practical workflow: always start with plain linear regression and evaluate the training-to-test gap. If the gap is small, regularization adds little value. If the gap is large, overfitting is present — try Lasso first if feature selection is desirable, Ridge if all features are believed to contribute, and Elastic Net when the situation is ambiguous.

The strength of regularization (α) is a hyperparameter — it is not learned from data but set by the analyst. Finding the optimal value requires cross-validation, a technique that evaluates model performance across multiple held-out subsets of the training data to identify the α that generalises best.

Closing Thoughts

Regression is deceptively simple to implement and genuinely difficult to implement well. The gap between running LinearRegression().fit(X_train, y_train) and building a model you can trust for decision-making lies in understanding what the model is doing, what assumptions it relies on, and where it will fail.

Regularization is not a correction applied when something goes wrong — it is a deliberate modelling choice that encodes prior knowledge about the problem: that coefficients should be small, that some features are likely irrelevant, or that the training data is noisy. Making that choice thoughtfully, rather than defaulting to whatever produces the highest R², is the difference between fitting data and building models that work.

Why Statistics Is the Backbone of Data Science

Kendi — Fri, 26 Jun 2026 05:55:28 +0000

One of the biggest surprises I had while learning data science was realizing that Python isn't the hard part.

You can learn Python in a few weeks. You can become comfortable with pandas pretty quickly. You can even train a machine learning model by following a tutorial.

But none of that means you understand your data.

That's where statistics comes in.

A lot of beginners (myself included) focus on learning tools first because they're exciting. New libraries, dashboards, machine learning models. Statistics often feels like something you can come back to later.

In reality, it's the opposite.

Statistics isn't a side topic in data science. It's the reason the tools work in the first place.

Data only becomes useful when you understand what it represents

Before running any analysis, the first question isn't "Which model should I use?"

It's "What kind of data am I looking at?"

Broadly speaking, data falls into two groups.

Numerical data consists of values you can measure or count. Sales, age, height, temperature.

Categorical data represents labels or groups. Blood type, product category, education level.

That distinction matters more than most beginners realize.

For example, calculating the average blood group doesn't make sense. Treating education levels as though the gap between each level is identical can also lead to misleading conclusions.

Python won't stop you from making those mistakes.

Statistics teaches you when a calculation actually makes sense.

A single number rarely tells the whole story

Imagine two classes that both have an average score of 50.

At first glance, you'd think they performed similarly.

class_A = [48, 49, 50, 51, 52]
class_B = [10, 30, 50, 70, 90]

Both classes have exactly the same mean.

But they're clearly very different.

In Class A, almost everyone performed similarly.

In Class B, performance varied dramatically.

That's why summary statistics come in pairs.

Measures like the mean, median, and mode tell you where the center of the data lies.

Measures like standard deviation, variance, range, and IQR tell you how spread out the data is.

Looking at only one is like reading only half the sentence.

The median isn't a backup plan

When people first learn statistics, they often think:

"Use the mean whenever possible. If it doesn't work, use the median."

That's not really how it works.

The mean uses every value in the dataset, which makes it powerful—but also sensitive to extreme values.

Imagine 99 people earn KES 30,000 each month, while one person earns KES 10 million.

The average income suddenly becomes much higher than what almost everyone actually earns.

The median ignores those extremes and simply finds the middle value.

Sometimes that's a much better description of what's "typical."

Choosing between the mean and median isn't about memorizing rules.

It's about understanding your data.

Outliers aren't always mistakes

One of the first instincts many people have is to delete values that look unusual.

Sometimes that's the right decision.

If someone accidentally entered 250 instead of 25, that's probably a data entry error.

But sometimes the unusual value is exactly what you're looking for.

If you're building a fraud detection system, the suspicious transactions are the most valuable observations in your dataset.

Statistics gives us a systematic way to flag potential outliers using the IQR rule.

Lower fence = Q1 − (1.5 × IQR)

Upper fence = Q3 + (1.5 × IQR)

Anything outside those boundaries is flagged for investigation.

Notice the wording.

Flagged—not automatically deleted.

Statistics helps identify unusual observations.

Context tells you what to do with them.

Machine learning doesn't replace statistics

Every machine learning algorithm makes assumptions.

Linear regression assumes linear relationships and normally distributed residuals.

Naive Bayes assumes features are conditionally independent.

K-Means works best when clusters are reasonably compact and roughly spherical.

If those assumptions don't hold, your model may still produce predictions.

They just won't be reliable.

Understanding statistics helps you know when to trust a model—and when not to.

The part of data science that doesn't become obsolete

Libraries change.

Frameworks change.

The code you write today may look outdated in a few years.

But the important questions stay the same.

What does this distribution tell me?
Is this difference meaningful or just random variation?
Is this outlier important or just an error?
Can I trust this result?

Those are statistical questions.

And they're the questions that separate someone who can write code from someone who can genuinely analyze data.

If you're starting your journey into data science, don't treat statistics as something to learn later.

It's the foundation that makes everything else make sense.

Power BI Data Modeling Explained Simply: Joins, Relationships, and Schemas

Kendi — Sat, 28 Mar 2026 20:43:42 +0000

Here is something nobody tells you when you start learning Power BI: the visuals are the easy part. Anyone can drag a bar chart onto a canvas. What separates a report that works from one that lies to you is what happens before you touch a single visual — the data model.

Get the model right and your numbers are accurate, your reports are fast, and your DAX measures are simple. Get it wrong and no amount of formatting or fancy visuals will fix it. You will just have a beautifully designed wrong answer.

This article covers everything you need to build models that actually work.

What is Data Modeling?

Data modeling is the process of organizing your tables and defining how they connect to each other so that analysis is accurate, efficient, and scalable.

In Power BI specifically, your data model determines three things:

Whether your calculations are correct
How fast your report runs
How easily someone can explore the data

A weak model produces incorrect aggregations, duplicate counts, and reports that take forever to load. A strong model makes all of that disappear.

Part 1: SQL Joins — Combining Tables in Power Query

Joins are how you physically combine two tables into one based on a shared column called a key. In Power BI, joins happen in Power Query during data preparation — before anything reaches your model.

Let us use a consistent example throughout. You have two tables:

Staff Table
| StaffID | StaffName |
|---------|-----------|
| S01 | James |
| S02 | Amina |
| S03 | Peter |

Training Table
| StaffID | Course |
|---------|--------|
| S01 | Excel |
| S02 | Power BI |
| S04 | SQL |

Notice: Peter (S03) has no training record. The SQL course (S04) has no matching staff member. This mismatch is exactly what each join type handles differently.

INNER JOIN — Only What Matches in Both

Returns rows that have a match in both tables. Anything without a match on either side is excluded.

Result:
| StaffID | StaffName | Course |
|---------|-----------|--------|
| S01 | James | Excel |
| S02 | Amina | Power BI |

Peter is excluded — no training record. The SQL course is excluded — no matching staff.

Use case: A clean attendance report showing only staff with confirmed training records.

LEFT JOIN — Keep Everything on the Left

Returns all rows from the left table. Rows from the right table are included only where a match exists. No match means null.

Result:
| StaffID | StaffName | Course |
|---------|-----------|--------|
| S01 | James | Excel |
| S02 | Amina | Power BI |
| S03 | Peter | null |

Peter stays — but his Course is null because no training record exists for him.

Use case: All staff members, flagging those who have not yet completed any training.

RIGHT JOIN — Keep Everything on the Right

Mirror of the LEFT JOIN. All rows from the right table are kept. Left table rows are included only where a match exists.

Result:
| StaffID | StaffName | Course |
|---------|-----------|--------|
| S01 | James | Excel |
| S02 | Amina | Power BI |
| S04 | null | SQL |

The SQL course stays — but StaffName is null because S04 does not exist in the Staff table.

Use case: All training courses offered, identifying any assigned to staff members who no longer exist in the system.

FULL OUTER JOIN — Everything from Both Tables

Returns every row from both tables. Where there is no match, nulls fill the gap on the missing side. Nothing is excluded.

Result:
| StaffID | StaffName | Course |
|---------|-----------|--------|
| S01 | James | Excel |
| S02 | Amina | Power BI |
| S03 | Peter | null |
| S04 | null | SQL |

Use case: A full audit comparing your HR system against your training system — surfacing every mismatch in both directions at once.

LEFT ANTI JOIN — Only the Unmatched Left Rows

Returns only rows from the left table that have no match in the right table. The opposite of an INNER JOIN in a sense.

Result:
| StaffID | StaffName |
|---------|-----------|
| S03 | Peter |

Only Peter — the staff member with no training record.

Use case: Finding staff members who have not attended any training. A compliance check.

RIGHT ANTI JOIN — Only the Unmatched Right Rows

Returns only rows from the right table that have no match in the left table.

Result:
| StaffID | Course |
|---------|--------|
| S04 | SQL |

Only the SQL course — assigned to a StaffID that does not exist.

Use case: Identifying orphaned records in a system — training records pointing to staff members who no longer exist.

How to Create Joins in Power Query

Go to Home tab → Transform Data to open Power Query Editor
Select the table you want as your left table
Click Home tab → Merge Queries
Select the second table from the dropdown
Click the matching column in each table to set the key
Choose your Join Kind from the dropdown
Click OK
Click the expand icon on the new merged column to select which columns to bring in

Join Summary Table

Join Type	Keeps from Left	Keeps from Right	Best For
Inner	Matching only	Matching only	Clean matched data
Left	All rows	Matching only	Enrich left, flag gaps
Right	Matching only	All rows	Enrich right, flag gaps
Full Outer	All rows	All rows	Full system audit
Left Anti	Unmatched only	Nothing	Find missing references
Right Anti	Nothing	Unmatched only	Find orphaned records

Part 2: Power BI Relationships — Joins vs Relationships

This distinction trips up almost everyone starting out.

A join physically merges two tables into one. The result is a single flat table. You use this during data preparation in Power Query.

A relationship keeps tables separate and creates a logical filter connection between them. The data stays in its own table. Power BI uses the relationship to know how filters should flow when someone interacts with a report.

In most professional Power BI models, relationships handle the core structure. Joins are reserved for specific data preparation steps where you genuinely need to flatten or enrich a table before loading it into the model.

	Joins	Relationships
Where	Power Query	Model View
Tables	Physically combined	Stay separate
Performance	Heavier	More efficient
Flexibility	Static	Dynamic
Use for	Data preparation	Analysis

How to Create Relationships in Power BI

Method 1 — Drag and drop in Model View:

Click the Model View icon on the left sidebar
Find the key column in one table
Drag it onto the matching key column in the other table
A relationship line connects the two tables

Method 2 — Manage Relationships:

Go to Modeling tab → Manage Relationships
Click New
Select both tables and their matching columns
Set cardinality and cross-filter direction
Click OK

Cardinality — What Kind of Relationship Is It?

One-to-Many (1:M) — Use This as Your Default

One row in the first table matches many rows in the second.

Example: One Department can have many Employees. The Departments table lists each department once. The Employees table has that department ID repeated across many rows.

This is the most common, most efficient, and most reliable relationship type in Power BI. Build your model around 1:M relationships wherever possible. In Model View it shows as 1 on one side and ***** on the other.

Many-to-Many (M:M) — Use With Real Caution

Many rows in both tables can match each other.

Example: Doctors and Hospitals. One doctor works at multiple hospitals. One hospital employs multiple doctors.

Power BI supports M:M natively but it introduces ambiguous filtering and can produce incorrect totals. Where possible, resolve M:M by introducing a bridge table — a third table that breaks the relationship into two clean 1:M relationships.

One-to-One (1:1) — Rare and Usually Unnecessary

Each row in one table matches exactly one row in the other.

If you have a 1:1 relationship, ask yourself honestly whether these tables should just be merged. They usually should.

Active vs Inactive Relationships

Power BI allows only one active relationship between any two tables. Active relationships are what visuals and DAX measures use by default. Additional relationships between the same tables must be inactive — shown as dashed lines in Model View.

When do you need inactive relationships?

A Date table connected to a fact table with multiple date columns is the classic case. Say your Orders table has OrderDate, ShipDate, and DeliveryDate. You can only have one active relationship to your Date table. The others are inactive.

To use an inactive relationship in a measure, activate it temporarily with USERELATIONSHIP:

Sales by Ship Date = CALCULATE(
    SUM(Orders[Revenue]),
    USERELATIONSHIP(Orders[ShipDate], Calendar[Date])
)

This measure calculates revenue filtered by ShipDate instead of the default OrderDate — without permanently changing the model.

Cross-Filter Direction — Which Way Do Filters Flow?

Single direction: Filters flow from the "one" side to the "many" side only. This is the default and the right choice for most relationships.

Both directions (bidirectional): Filters flow both ways simultaneously.

Bidirectional sounds helpful but it creates real problems — ambiguous filter paths, performance degradation, and measures that produce incorrect results in ways that are very hard to diagnose. Start with single direction always. Only consider bidirectional after testing confirms you genuinely need it and the behavior is correct.

Part 3: Fact Tables and Dimension Tables

Every professional data model is built around this distinction.

Fact Tables

A fact table stores measurable events — things that happened. Each row is one transaction or event.

Contains numbers you want to measure: revenue, quantity, duration, count
Contains foreign keys that point to dimension tables
Typically long — many rows
Typically narrow — few columns

Examples: Hospital admissions, e-commerce orders, bank transactions, flight bookings

Dimension Tables

A dimension table stores descriptive context about those events — the who, what, where, and when.

Contains descriptive attributes: names, categories, locations, dates
Contains a primary key that the fact table references
Typically short — few rows
Typically wide — many columns

Examples: Patients, Products, Branches, Calendar

The practical rule: Your slicers and filters come from dimension tables. Your aggregations and measures come from fact tables. Keep them separate.

Part 4: Schemas — The Overall Shape of Your Model

Star Schema — Start Here Every Time

A central fact table connects directly to surrounding dimension tables. One hop from any dimension to the fact. No chains.

              [Calendar]
                  |
[Customer] — [Sales Fact] — [Product]
                  |
              [Branch]

Clean, fast, easy to maintain. Power BI's calculation engine is specifically optimized for this structure. Your DAX measures will be simpler and your reports will be faster on a star schema than any other structure. This is the recommended starting point for almost every Power BI model.

Snowflake Schema — Normalized but Complex

Dimension tables are broken into sub-dimensions connected in chains.

[Region] ← [Branch] — [Sales Fact] — [Product] → [Category] → [Department]

Instead of a single Branch dimension with Region included as a column, Region becomes its own separate table connected to Branch.

This reduces data redundancy — useful in enterprise data warehouses where storage and integrity matter. In Power BI reporting however, the additional complexity adds joins, slows queries, and makes the model harder to navigate. If your source data arrives in a snowflake structure, consider flattening dimension chains in Power Query before loading into the model.

Flat Table (Denormalized) — Simple but Limited

Everything — facts and dimensions — in a single table. No relationships needed.

OrderDate	CustomerName	City	ProductName	Category	Revenue
01/03/2026	James	Nairobi	Laptop	Electronics	85000
02/03/2026	Amina	Mombasa	Phone	Electronics	42000

Fast to build, easy to understand. But "Electronics" is repeated in every electronics row. Change a category name and you are updating thousands of cells. Performance degrades quickly as row count grows.

Use flat tables for: Quick one-off analysis, very small datasets, early prototyping before building a proper model. Avoid them for anything that will be maintained, updated, or scaled.

Schema Comparison

Schema	Structure	Performance	Complexity	Best For
Star	Fact + direct dimensions	Excellent	Low	Most Power BI reporting
Snowflake	Fact + chained dimensions	Good	Medium	Enterprise warehouses
Flat	Single table	Poor at scale	Very low	Small, simple, one-off

Part 5: Role-Playing Dimensions

A role-playing dimension is one dimension table used multiple times in the same model, each time in a different context.

The Date table is the most common example. A Hospital fact table might have AdmissionDate, DischargeDate, and SurgeryDate — all referencing the same Calendar dimension but each representing a different point in time.

Solution 1 — One active, rest inactive:
Create one active relationship between Calendar and AdmissionDate. Create inactive relationships to DischargeDate and SurgeryDate. Use USERELATIONSHIP in DAX when you need the inactive ones.

Discharges This Month = CALCULATE(
    COUNT(Admissions[PatientID]),
    USERELATIONSHIP(Admissions[DischargeDate], Calendar[Date])
)

Solution 2 — Duplicate the dimension table:
Create three separate Calendar tables — AdmissionCalendar, DischargeCalendar, SurgeryCalendar — each with its own active relationship. More relationships to maintain but no inactive relationship complexity in DAX.

For most scenarios, Solution 1 is cleaner.

Part 6: Common Modeling Issues

Many-to-Many without a bridge table
Connecting two fact tables directly produces unreliable totals. Fix by introducing a shared dimension table that both relate to through 1:M relationships.

Bidirectional filtering everywhere
Slows the model and creates ambiguous results. Default to single direction. Only use Both direction after deliberate testing.

Circular relationships
Table A → Table B → Table C → Table A creates a loop Power BI cannot resolve. Fix by identifying and removing the redundant relationship.

No dedicated Date table
Using date columns from fact tables directly breaks time intelligence functions. Always create a continuous Date dimension table, mark it as a Date table in Power BI (right-click table in Model View → Mark as Date Table), and use it as the single source for all date filtering.

Loading unnecessary columns
Every column you load into the model consumes memory. In Power Query, remove columns you will not use before loading. Keep the model lean.

The Recommended Workflow

Step 1 — Load your data into Power Query

Step 2 — Clean and prepare using joins and transformations in Power Query

Step 3 — Build the model in Model View using 1:M relationships in a star schema

Step 4 — Create a Date table and mark it appropriately

Step 5 — Write DAX measures on top of the clean model

Following this sequence means your measures are built on a solid foundation. Skipping to DAX before the model is right is the most common reason Power BI reports produce numbers nobody trusts.

Final Thought

The most important insight in data modeling is also the simplest one: keep facts and dimensions separate, connect them with 1:M relationships, and structure them as a star.

Everything else in this article — the join types, cardinality options, schema variations, role-playing dimensions — is either building on that foundation or explaining what happens when you deviate from it.

Master the star schema with clean 1:M relationships first. You will handle 90% of real-world Power BI modeling scenarios with that alone.

Part of my data science learning journey.

How Excel is Used in Real-World Data Analysis

Kendi — Wed, 25 Mar 2026 13:10:38 +0000

How Excel is Used in Real-World Data Analysis

Excel is a spreadsheet application by Microsoft that organizes data into a grid of rows and columns. Each box in that grid is called a cell, and each cell can hold a number, text, a date, or a formula that calculates something automatically.

On top of that grid, Excel gives you:

Hundreds of built-in functions for math, text, dates, and logic
Power Query for importing and transforming data from any source
PivotTables that summarize thousands of rows in seconds
Charts and visualizations that turn numbers into stories
Data validation to control what gets entered into cells
Conditional formatting that highlights patterns automatically

Excel is used in finance, healthcare, logistics, marketing, research, and government — basically anywhere data exists and decisions need to be made. It sits at a sweet spot between accessibility and depth. A beginner can use it on day one. An expert can still find new things to learn after years.

How I Used Excel in a Real Project

For a recent project I analyzed product performance data from Jumia — one of Africa's largest e-commerce platforms — covering product prices, discounts, customer reviews, and ratings. Here is exactly how I approached it.

1. Make a Copy of Your Original Data

Before touching anything, I created a copy of the raw data and kept the original sheet untouched. Data cleaning is destructive — you delete rows, overwrite values, change formats. Without a backup, there is no going back.

2. Understand Your Data Before Changing Anything

I used CTRL + END to see how many rows and columns I had, then scrolled through to spot obvious problems — price columns with "KSh" symbols, ratings stored as text like "4.5 out of 5", negative review counts, and blank cells scattered throughout.

Understanding your data first tells you exactly what needs fixing and in what order. It prevents you from applying the wrong solution to the wrong problem.

3. Data Cleaning — Where the Real Work Happens

Data cleaning is unglamorous but it is the foundation everything else stands on. In professional data work it takes 60 to 80 percent of total project time. The key steps I worked through were:

Removing duplicates — ensuring each product appeared only once using Data tab → Remove Duplicates
Fixing data types — converting price columns from text to numbers, extracting numeric ratings from text like "4.5 out of 5"
Handling missing values — replacing blank cells with the column average rather than deleting rows and losing data
Standardizing text — making sure category values like "Nairobi" and "nairobi" were consistent using PROPER, UPPER, and LOWER functions

None of this is exciting. All of it is essential.

The Formulas That Fascinated Me

XLOOKUP — The Lookup Function Excel Should Have Had From the Start

Before XLOOKUP, the standard way to look up data was VLOOKUP — and it had serious limitations. It could only search left to right, broke silently when columns were inserted, and required hardcoded column numbers that made formulas fragile.

XLOOKUP fixes all of that:

=XLOOKUP(lookup_value, lookup_range, return_range, if_not_found)

It searches in any direction, handles missing values natively, uses column names instead of numbers, and can return multiple columns at once. One formula replaces what used to require complex workarounds.

The one caveat — XLOOKUP is only available in Excel 2021 and Microsoft 365. If you are on an older version, INDEX/MATCH is the next best alternative.

IFS — Replacing Messy Nested IFs

IFS checks multiple conditions in sequence and returns the first match. It replaced what would otherwise be deeply nested IF statements that are almost impossible to read or debug.

For example, classifying products by rating:

=IFS(F2>=4.5, "Excellent", F2>=3, "Average", F2<3, "Poor")

Clean, readable, and easy to update. Nesting three IFs inside each other to do the same thing is a maintenance nightmare.

PivotTables — Summarizing Data Without a Single Formula

A PivotTable takes your raw data and lets you summarize it any way you want — by category, by date, by product — without writing a single formula. You drag fields into rows, columns, and values, and Excel does the rest.

One tip that makes PivotTables significantly more reliable: convert your clean data into an Excel Table first using CTRL + T. Tables expand automatically when new rows are added, so your PivotTable always captures the full dataset on refresh.

The Dashboard — Making Data Usable for Everyone

The final step was bringing everything together in an interactive dashboard — a single sheet where any business stakeholder could explore the data without touching the underlying numbers. KPI cards at the top, charts in the middle, and slicers that filter every chart simultaneously with a single click.

This is the difference between data analysis and data communication. The numbers only matter if the right people can read them.

Conclusion

The formulas are learnable. The functions have documentation. What nobody tells you is that the hardest part of data analysis is not the technical side — it is the discipline.

The discipline to make a backup before touching anything. The discipline to understand your data before changing it. The discipline to clean thoroughly before analyzing. The discipline to question your results before presenting them.

Excel gave me powerful tools. But the two steps that made the biggest difference were the simplest ones — making a copy of the original data and actually reading through it before writing a single formula.

Written as part of a data science learning program at LuxDev HQ.