Power Bi,Modelling,Relationships and Joins

jmuthama12 — Mon, 14 Sep 2026 11:11:20 +0000

Power BI Data Modelling, Relationships and Joins:

When I first started working with Power BI, I thought building a report was mainly about choosing charts. I quickly learned that the real work happens behind the visuals.

A good Power BI report starts with a good data model.

1._ Data Modelling in Power BI_

Data modelling is the process of organising tables and defining how they relate to each other. A good model improves DAX, performance, scalability, reporting and maintenance.

Flat Table

Everything is stored in one large table.

Sales → Customer + Product + Date + Location + Amount

Pros: Simple and easy to understand.
Cons: Repeated data, larger models and harder maintenance.

It works for small datasets, but becomes inefficient as data grows.

Star Schema

A central fact table connects directly to several dimension tables.

DimCustomer → FactSales ← DimProduct
↑
DimDate

This is usually my preferred approach because it is simple, fast and DAX-friendly.

Snowflake Schema

Dimensions are further split into related tables.

DimCategory → DimProduct → FactSales
DimCountry → DimCustomer → FactSales

It reduces duplication but creates more relationships and complexity. It can be useful for large, highly normalised datasets, but I would normally prefer a star schema in Power BI.

Fact and Dimension Tables

A fact table records business events and numeric values.

Example:

FactSales: SalesID, CustomerID, ProductID, DateID, Quantity, Revenue

A dimension table describes those events.

DimCustomer: CustomerID, Name, Region
DimProduct: ProductID, ProductName, Category
DimDate: DateID, Date, Month, Year

The most important concept is grain—what one row represents. For example, one row in FactSales might represent one product sold in one transaction.

3._ Relationships_

A relationship tells Power BI how tables are connected.

The common types are:

1: One-to-Many:* one customer can have many sales. This is the normal star-schema relationship.
1:1 One-to-One: each record matches one record. Useful only in specific cases.
: Many-to-Many:* multiple records match multiple records. Use carefully because it can create ambiguous filtering.

Usually, DimCustomer[CustomerID] is unique, while FactSales[CustomerID] can appear many times. The first is a primary key; the second is a foreign key.

Active relationships are used by default. Inactive relationships can be activated in DAX when needed.

Filter Direction

With single-direction filtering, a selection in DimProduct filters FactSales.

DimProduct → FactSales

Bidirectional filtering allows filters to travel both ways, but it should be used carefully. It can create ambiguous filter paths and unnecessary complexity.

Joins in Power Query

A join combines tables using Merge Queries.

Using Customers and Orders:

Join Records retained
Left Outer All Customers + matching Orders
Right Outer All Orders + matching Customers
Full Outer All records from both
Inner Only matching records
Left Anti Customers without Orders
Right Anti Orders without Customers

Joins are useful when you actually need to bring columns or rows together during data preparation.

Joins vs Relationships

This distinction confused me at first.

A Power Query merge physically combines data before it enters the model.

A Power BI relationship does not combine tables. It simply tells Power BI how tables should filter each other.

Therefore, I would avoid excessive merging. Keeping FactSales, DimCustomer, DimProduct and DimDate separate usually produces a cleaner model.

My Recommended Model

For most business intelligence projects, I would choose a Star Schema:

Dimensions → Fact Table

I would normally use 1: relationships*, with dimensions on the “1” side and facts on the “*” side, using single-direction filtering.

Why?

It gives me better performance, simpler DAX, less redundancy, easier reporting, clearer relationships and easier maintenance.

Power BI is not just about making beautiful dashboards. A beautiful dashboard built on a poor model will eventually become a painful dashboard to maintain.

Build the model well, and the report becomes much easier.

EXCEL AS A DATA ANALYSIS TOOL

jmuthama12 — Sun, 22 Mar 2026 18:11:32 +0000

Microsoft Excel is one of the most important tools in data analysis. Whether you're cleaning data, building reports, or preparing datasets for pipelines, Excel skills are essential.
BASIC EXCEL FUNCTIONS
➤ SUM
Adds values in a range
=SUM(A1:A10)

➤ AVERAGE

Finds the mean
=AVERAGE(A1:A10)
COUNT
Counts numeric values
=COUNT(A1:A10)

Logical Functions
➤ IF
=IF(A1>50, "Pass", "Fail")

Returns values based on a condition

➤ AND
=AND(A1>50, B1="Yes")

Checks multiple conditions
➤ OR
=OR(A1>50, B1="Yes")

Returns TRUE if one condition is met

** Lookup & Reference Functions**
➤ VLOOKUP
=VLOOKUP(A2, A1:C10, 3, FALSE)

Searches vertically
➤ HLOOKUP
=HLOOKUP(A2, A1:C3, 2, FALSE)

Searches horizontally
➤ INDEX + MATCH (Better alternative)
=INDEX(B1:B10, MATCH(A2, A1:A10, 0))
➤ LEN

Counts characters
=LEN(A1)
CONCAT / CONCATENATE

Join text
=CONCAT(A1, " ", B1)

These are some of the formulae's used daily in excel

DEV Community: jmuthama12

Power Bi,Modelling,Relationships and Joins

EXCEL AS A DATA ANALYSIS TOOL