A practical guide to connecting your data and turning it into powerful visuals
Introduction
When you first open Power BI and load multiple tables, one of the first things you'll encounter is the Model view — a place where your tables sit like islands, waiting to be connected. Understanding how to connect those tables, and then turn the results into a dashboard, is one of the most important skills in data analytics.
In this article, we'll break down four foundational concepts:
- Relationships — how tables talk to each other in Power BI
- Joins — how rows from different tables are combined
- Schemas — how your tables are organised and structured
- Dashboards — how to present your insights visually
By the end, you'll have a clear mental model of how Power BI handles data behind the scenes — and how to make smarter decisions in your reports.
Part 1: What Are Relationships in Power BI?
A relationship in Power BI is a link between two tables based on a shared column — called a key. This is what allows Power BI to know that a sale in your Sales table belongs to a specific customer in your Customers table.
Why Do Relationships Matter?
Imagine you have two tables:
Customers Table
| CustomerID | CustomerName | City |
|---|---|---|
| 1 | Alice | Nairobi |
| 2 | Bob | Mombasa |
| 3 | Carol | Kisumu |
Sales Table
| SaleID | CustomerID | Amount |
|---|---|---|
| 101 | 1 | 5,000 |
| 102 | 2 | 3,200 |
| 103 | 1 | 7,800 |
The column CustomerID appears in both tables. By creating a relationship on this column, Power BI can tell you that Alice (CustomerID 1) made purchases totalling KES 12,800.
Without a relationship, Power BI treats these as completely separate tables — your visuals won't be able to combine data from both.
How to Create a Relationship in Power BI
- Go to the Model view (the icon with three connected shapes on the left panel).
- Drag the
CustomerIDcolumn from the Customers table onto theCustomerIDcolumn in the Sales table. - Power BI creates a line between the two tables showing the relationship.
You can also go to Home → Manage Relationships → New to set one up manually.
The Model view in Power BI Desktop — tables are connected by dragging shared key columns together.
Part 2: Cardinality — The "Type" of Relationship
When you create a relationship, Power BI assigns it a cardinality, which describes how many rows in one table match rows in the other.
One-to-Many (1:*)
This is the most common relationship type. One row in Table A can match many rows in Table B.
Example: One customer can have many sales. One product can appear in many orders.
- The "one" side is usually the lookup/dimension table (e.g., Customers, Products)
- The "many" side is usually the fact table (e.g., Sales, Orders)
Many-to-One (*:1)
This is the same as one-to-many, just viewed from the opposite direction. Power BI treats them identically.
One-to-One (1:1)
Each row in Table A matches exactly one row in Table B — and vice versa.
Example: A table of employees and a table of employee ID cards.
Use this sparingly. If two tables are one-to-one, you could often just merge them into a single table.
Many-to-Many (:)
Multiple rows in Table A can match multiple rows in Table B.
Example: Students and Courses — one student takes many courses, and each course has many students.
Many-to-many relationships can be tricky and may cause unexpected results. As a beginner, try to avoid them until you're comfortable with the basics.
Part 3: Cross-Filter Direction
When you connect two tables, Power BI also sets a cross-filter direction — this controls how filters flow between tables when you interact with visuals.
Single Direction (→)
Filters flow from one table to another in one direction only. This is the default and recommended setting for most cases.
Example: Filtering by a product category in the Products table will filter the Sales table. But filtering in Sales won't filter Products.
Both Directions (↔)
Filters flow in both directions simultaneously.
Use "Both" with caution. It can cause ambiguous results and slow down your reports, especially in larger data models.
Part 4: What Are Joins?
A join is the operation that combines rows from two or more tables based on a related column. Joins are a concept from SQL (databases), but Power BI performs joins behind the scenes when you set up relationships or merge queries in Power Query.
Understanding joins helps you know which rows will appear in your final report.
The four main join types illustrated. Power BI supports all of these through Power Query's Merge Queries feature.
Inner Join
Returns only the rows that have a matching value in both tables.
Example: If CustomerID 4 exists in Sales but not in Customers, that sale will be excluded from the result.
Customers INNER JOIN Sales ON CustomerID
→ Only customers who have made at least one sale appear
Left Join (Left Outer Join)
Returns all rows from the left table, and the matching rows from the right table. If there's no match, the right side shows blanks/nulls.
Example: All customers appear, even those who haven't made any sales yet.
Customers LEFT JOIN Sales ON CustomerID
→ All customers appear; sales columns are blank for those with no sales
Right Join (Right Outer Join)
Returns all rows from the right table, and the matching rows from the left table.
Full Outer Join
Returns all rows from both tables, with nulls where there's no match on either side.
How to Apply Joins in Power BI (Power Query)
- In Power Query Editor, select a table.
- Go to Home → Merge Queries.
- Choose the second table and the matching columns.
- Select the Join Kind (Inner, Left Outer, Right Outer, Full Outer, etc.).
- Click OK and expand the merged column to access the data.
The Merge Queries dialog in Power Query — this is where you select your join type and matching columns.
Part 5: What Are Schemas?
A schema is the overall blueprint of how your tables are organised and how they relate to each other. In Power BI, you'll typically work with one of two schema types: Star Schema or Snowflake Schema.
Star Schema ⭐
The star schema is the most recommended structure for Power BI reports. It has:
- One central fact table — contains measurable, transactional data (e.g., Sales, Revenue, Orders)
- Multiple dimension tables surrounding it — contain descriptive attributes (e.g., Customers, Products, Dates, Locations)
[Date Table]
|
[Products] — [Sales Fact Table] — [Customers]
|
[Stores Table]
A classic Star Schema in Power BI — one central fact table connected to several dimension tables.
Why is Star Schema great for Power BI?
- Simpler relationships (mostly one-to-many)
- Faster report performance
- Easier to understand and maintain
- DAX calculations work more predictably
Practical Example:
| Table | Type | Contains |
|---|---|---|
| Sales | Fact | SaleID, Date, ProductID, Amount |
| Products | Dimension | ProductID, Name, Category |
| Customers | Dimension | CustomerID, Name, City |
| Date | Dimension | Date, Month, Quarter, Year |
Snowflake Schema ❄️
A snowflake schema is an extension of the star schema where dimension tables are further broken down into sub-dimensions.
[Category] → [Products] → [Sales Fact Table] → [Customers] → [Region]
Pros:
- Less data repetition (more normalised)
- Smaller storage size
Cons:
- More complex relationships
- Slower performance in Power BI
- Harder to manage
For most Power BI projects, stick to a **Star Schema. It's simpler, faster, and Power BI is optimised for it.
Part 6: The Date Table — A Must-Have
One dimension table that every Power BI model should have is a dedicated Date table. This is a table with one row per calendar day, along with columns like Month, Quarter, Year, and Week Number.
Having a proper Date table allows you to:
- Use time intelligence functions in DAX (e.g., year-to-date totals, month-over-month comparisons)
- Filter reports cleanly by date periods
- Avoid gaps in your timeline visuals
How to Mark a Table as a Date Table
- Select your Date table in the Model view.
- Right-click → Mark as Date Table.
- Select the column that contains unique dates.
Part 7: Power BI Dashboards
Now that your data model is set up with proper relationships and a clean schema, the final step is to visualize your insights — and this is where dashboards come in.
What Is a Power BI Dashboard?
A dashboard in Power BI is a single-page canvas that displays tiles — visual summaries of your key metrics. Think of it as your data's "control panel": you look at it and instantly know how your business or project is performing.
Dashboards are different from reports:
| Feature | Report | Dashboard |
|---|---|---|
| Pages | Can have multiple pages | Always one page (canvas) |
| Interactivity | High — slicers, drilldowns, filters | Limited — click to open report |
| Purpose | Deep exploration and analysis | High-level overview and monitoring |
| Created from | Datasets directly | Pinning visuals from reports |
| Real-time data | Not always | Yes — supports live tiles |
In simple terms: you build a **report* to analyze data, then you pin the most important visuals from that report to a dashboard for quick monitoring.*
Key Components of a Dashboard
1. KPI Cards
These show a single important number — like total revenue, number of sales, or percentage growth. They're the first thing you read on a dashboard.
2. Charts and Graphs
- Bar/Column charts — compare values across categories (e.g., sales by region)
- Line charts — show trends over time (e.g., monthly revenue)
- Pie/Donut charts — show proportions (e.g., sales by product category)
3. Tables and Matrices
Good for showing detailed breakdowns when you need more than just a chart.
4. Slicers
Interactive filters that let viewers narrow down what they see — by date, region, product, etc.
5. Maps
Visualize geographic data — sales by city, customers by county, etc.
A sample Power BI Sales & Marketing dashboard — notice the mix of KPI tiles, charts, and trend lines.
How to Build Your First Dashboard
Step 1: Build your report first
- Open Power BI Desktop.
- Load your data (Excel, CSV, or database).
- Set up relationships in the Model view.
- Go to the Report view and start adding visuals from the Visualizations pane on the right.
Step 2: Publish to Power BI Service
- Click Home → Publish.
- Sign in to your Power BI account.
- Choose a workspace and click Publish.
Step 3: Pin visuals to a Dashboard
- Open your published report in Power BI Service (app.powerbi.com).
- Hover over a visual — a pin icon (📌) will appear.
- Click the pin icon → choose New Dashboard or an existing one.
- Repeat for each visual you want on the dashboard.
Step 4: Arrange your tiles
- In the dashboard view, drag and resize tiles to arrange them logically.
- Put KPI cards at the top for quick reading.
- Place detailed charts below.
Dashboard Design Tips for Beginners
- Less is more — don't cram 20 visuals onto one dashboard. Pick the 5–8 most important ones.
- Use a consistent colour theme — pick 2–3 colours and stick with them.
- Always label your visuals — add titles so viewers know what they're looking at.
- Put the most important metric top-left — that's where the eye naturally goes first.
- Use slicers — let the viewer filter by date or category without leaving the dashboard.
A good dashboard answers one question at a glance: "How are we doing?"
Part 8: Common Beginner Mistakes to Avoid
1. Using Many-to-Many relationships without understanding them
Many-to-many relationships can cause double-counting and confusing results. Always try to resolve them by introducing a bridge table or restructuring your data.
2. Not having a Date table
Relying on date columns scattered across your fact table limits what you can do with time analysis. Always build or import a proper calendar/date table.
3. Relationship direction confusion
Make sure filters flow in the direction that makes sense for your analysis. Default to single-direction and only use bidirectional when absolutely necessary.
4. Duplicates in the "one" side of a relationship
If the column you're using on the "one" side has duplicate values, Power BI will throw an error or switch the relationship to many-to-many. Always ensure your dimension table key column has unique values.
5. Building the dashboard before the model
Many beginners jump straight into visuals without validating the data model. Always check your relationships in the Model view first — a broken model gives you wrong numbers in your dashboard.
6. Overloading the dashboard
A dashboard with 15 charts is just as confusing as raw data. Focus on the metrics that matter most.
Summary
Here's a quick recap of everything we've covered:
| Concept | What It Means |
|---|---|
| Relationship | A link between two tables based on a shared key column |
| Cardinality | The type of match between rows (1:1, 1:Many, Many:Many) |
| Cross-filter | The direction filters travel between related tables |
| Inner Join | Returns only rows with matches in both tables |
| Left Join | Returns all rows from the left table, nulls for no matches |
| Full Outer Join | Returns all rows from both tables |
| Star Schema | One fact table surrounded by dimension tables |
| Snowflake Schema | Star schema with further-normalised dimension tables |
| Dashboard | A single-page visual summary of your key metrics |
| Report vs Dashboard | Reports are for deep analysis; dashboards are for monitoring |
Final Thoughts
Relationships, joins, schemas, and dashboards might feel abstract at first, but they become second nature with practice. Every time you load a new dataset into Power BI, ask yourself:
- What is my fact table? (the thing I'm measuring)
- What are my dimension tables? (the things I'm grouping or filtering by)
- How should these tables relate to each other?
- Are my keys unique on the "one" side of the relationship?
- What 5–8 key metrics should go on my dashboard?
Getting these foundations right means your reports will be faster, your calculations will be accurate, and your dashboards will make sense to anyone who looks at them.
Happy modelling! 🚀
*Written as part of my data analytics learning journey at LuxDevHQ Bootcamp.
Top comments (0)