Master Power BI From Scratch

Learn Power BI fundamentals, data sources, data transformation, data modeling, DAX, visualizations, reports, and dashboards through structured practical lessons.

COURSE SYLLABUS
1. Power BI Introduction
Introduction to Power BI Power BI Desktop and Service Power BI Workflow Power BI Terminology Power BI Workspace Overview
2. Power BI Installation and Interface
Power BI Installation Power BI Desktop Interface Report, Data and Model Views Power BI File Management Power BI Settings and Options
3. Power BI Data Sources
Excel and CSV Data Sources Text and Folder Data Sources Database Data Sources Web and Online Data Sources Data Source Credentials and Refresh
4. Power BI Data Transformation
Power Query Introduction Data Types and Column Operations Merge and Append Queries Split, Group and Pivot Columns Power Query Applied Steps
5. Power BI Data Cleaning
Missing and Null Values Remove Duplicates and Errors Text and Date Cleaning Replace and Fill Values Data Quality Validation
6. Power BI Data Modeling
Data Modeling Fundamentals Create Table Relationships Cardinality and Filter Direction Star Schema and Dimension Tables Date Tables and Model Optimization
7. DAX
DAX Introduction and Syntax DAX Aggregation Functions DAX Logical and Conditional Functions DAX Filter and Iterator Functions DAX Date and Time Intelligence
8. Power BI Calculations
Calculated Columns Measures and Implicit Measures Row Context and Filter Context Quick Measures and KPI Time-Based Business Calculations
9. Power BI Visualizations
Basic Power BI Charts Pie, Donut and Treemap Charts Cards, Tables and Matrices Maps and Geographic Visuals Advanced and Custom Visuals
10. Power BI Filters and Slicers
Filter Types and Filter Pane Slicers and Sync Slicers Drill Down and Drill Through Cross-Filtering and Highlighting Bookmarks and Buttons
11. Power BI Reports and Dashboards
Report Page Layout and Design Multi-Page Reports Dashboard Creation and Tiles Executive and Business Reports Report Usability and Accessibility
12. Power BI Formatting and Interactivity
Themes and Color Formatting Conditional Formatting Tooltips and Report Page Tooltips Visual Interactions and Edit Interactions Mobile Layout and Responsive Reports
13. Power BI Publishing and Sharing
Publish Reports to Service Workspaces and Apps Scheduled Refresh and Gateway Sharing and Permissions Row-Level Security and Deployment
14. Power BI Projects and Assessment
Sales Dashboard Project Student Performance Project Data Cleaning and Modeling Assignment DAX and Visualization Assessment Final Power BI Project and Assessment

Create Table Relationships

Learn how to create, configure, test, and troubleshoot table relationships in Power BI. Relationships connect related tables so that filters and calculations can work across the data model.

1. What Is a Table Relationship?

A table relationship connects two tables through columns containing corresponding values. For example, a Sales table may contain ProductID values that correspond to ProductID values in a Products table.

When the relationship is configured correctly, a user can select a product category in a report and analyze the sales associated with that category.

Why Relationships Are Useful

  • Connect transactional data with descriptive information.
  • Allow dimension fields to filter related fact records.
  • Support calculations across multiple tables.
  • Reduce the need to repeat descriptive columns in every table.
  • Help organize a model for interactive reporting.
Products ProductID ProductName Category Sales OrderID ProductID SalesAmount 1 : * ProductID connects each product to its sales records.

Visual 1: A one-to-many relationship between Products and Sales.

2. Prepare Tables Before Creating Relationships

Before connecting tables, verify that the matching columns contain compatible values and data types. A relationship may fail or behave unexpectedly when keys contain duplicates, nulls, inconsistent formats, or incompatible data types.

Check What to Verify Example
Matching columns Both tables contain corresponding key fields. Products[ProductID] and Sales[ProductID]
Data types Key columns use compatible data types. Both ProductID columns use text.
Unique dimension keys The one-side column contains unique values. Each ProductID appears once in Products.
Missing values Required keys are not unexpectedly blank. Sales rows have valid ProductID values.
Matching records Fact-table keys correspond to expected dimension records. Sales ProductID values exist in Products.
Best practice: Clean and validate key columns in Power Query before creating relationships. This makes the model easier to troubleshoot.

3. Create a Relationship in Model View

Model view displays tables and their relationships visually. You can create a relationship by dragging a column from one table to its matching column in another table or by using the Manage relationships dialog.

Method A: Drag and Drop

  1. Open Power BI Desktop.
  2. Load the Products and Sales tables.
  3. Select Model view from the left navigation.
  4. Locate ProductID in the Products table.
  5. Drag Products[ProductID] onto Sales[ProductID].
  6. Review the relationship settings in the dialog.
  7. Confirm the cardinality and cross-filter direction.
  8. Select OK to create the relationship.

Method B: Manage Relationships

  1. Open the Modeling tab.
  2. Select Manage relationships.
  3. Select New.
  4. Choose the first table and its key column.
  5. Choose the second table and the matching key column.
  6. Set the appropriate cardinality and filter direction.
  7. Confirm the settings and create the relationship.
Create a Relationship Select Keys Match columns Configure Relationship options Create Confirm settings Test Check report results Verify the key columns and relationship settings before saving. Test with a simple visual to confirm that filters work as expected.

Visual 2: The basic process for creating and testing a relationship.

4. Configure Relationship Properties

Power BI relationship settings control how tables connect and how filters move through the model. The correct settings depend on the uniqueness of the keys and the intended analysis.

Property Purpose Typical Choice
Table and column Identifies the columns being connected. Products[ProductID] and Sales[ProductID]
Cardinality Defines how rows correspond between tables. One-to-many for Products to Sales
Cross-filter direction Defines how filters propagate through the relationship. Single direction for many common dimension-to-fact models
Active status Determines whether the relationship is used by default. Active when it is the intended default filter path

Active and Inactive Relationships

An active relationship is used by default when filter context travels through the model. An inactive relationship is retained in the model but does not normally propagate filters unless a supported DAX expression activates it for a calculation.

Inactive relationships can be useful when two tables have multiple valid relationships, such as an Orders table containing both OrderDate and ShipDate.

Multiple Date Relationships Orders OrderDate ShipDate Date Table Date Year, Month Active Inactive Only one of these direct relationships can be active at a time.

Visual 3: A model may use an active OrderDate relationship and an inactive ShipDate relationship.

5. Test Whether the Relationship Works

After creating a relationship, test it using a simple report visual. A relationship can exist in the model but still produce unexpected results if the keys, cardinality, or filter direction are incorrect.

Testing Procedure

  1. Open Report view.
  2. Add ProductName from the Products table to a table visual.
  3. Add a Total Sales measure using the Sales table.
  4. Check whether the sales values appear for the expected products.
  5. Add Category as a slicer or filter.
  6. Change the category selection and inspect the results.
  7. Investigate blank or unexpected totals.
Total Sales = SUM ( Sales[SalesAmount] )
Relationship Test: Sales by Product Product Category Total Sales Laptop Computers 45,000 Monitor Displays 18,000 Keyboard Accessories 4,500 Illustrative values only; your results depend on your dataset.

Visual 4: Use a table visual to check whether product filters affect sales correctly.

Tip: If every product shows the same sales total, check whether the relationship is active and whether the correct columns are connected.

6. Troubleshoot Common Relationship Problems

Problem Possible Cause What to Check
Relationship cannot be created Duplicate values on the one side or incompatible key columns. Check uniqueness and data types.
Blank category appears in a visual Some fact-table keys may not match dimension keys. Inspect unmatched keys and missing dimension records.
Filters do not affect a measure Relationship may be inactive or the filter path may be incorrect. Review active status and cross-filter direction.
Totals appear too high Duplicate records, incorrect granularity, or unsuitable relationship design. Check table grain, keys, and aggregation logic.
Ambiguous relationships Multiple paths may connect tables. Review relationship paths and simplify the model where appropriate.
Important: Do not change a relationship to many-to-many just to make an error disappear. First determine why the key values are not unique and whether the model design correctly represents the business process.

7. Practical Exercise

Exercise: Connect Products and Sales

Use the following tables in Power BI Desktop:

  • Products: ProductID, ProductName, Category.
  • Sales: OrderID, ProductID, Quantity, SalesAmount.
  1. Load both tables into Power BI.
  2. Verify that Products[ProductID] contains unique values.
  3. Check that both ProductID columns use compatible data types.
  4. Create a one-to-many relationship from Products to Sales.
  5. Review the cross-filter direction and active status.
  6. Create a Total Sales measure.
  7. Build a table visual containing ProductName and Total Sales.
  8. Add Category to a slicer and test the results.
  9. Investigate any unmatched product identifiers.
  10. Document the relationship settings and validation results.

Expected outcome: A working relationship that allows product fields to filter sales calculations.

8. Knowledge Check

Question 1: What is a table relationship?

A. A visual formatting option

B. A connection between matching columns in related tables

C. A report title

D. A type of chart


Question 2: Which table commonly contains the unique ProductID values?

A. Products dimension table

B. Sales transaction table only

C. Report page table

D. Visual formatting table


Question 3: What should you check before creating a relationship?

A. Only the report theme

B. Only the page size

C. Key values, uniqueness, and compatible data types

D. Only the chart title


Question 4: What is an active relationship?

A. A relationship used by default for filter propagation

B. A relationship that deletes records

C. A hidden report page

D. A relationship that changes column names


Question 5: What should you do if product filters do not affect sales?

A. Delete every table

B. Check the relationship, filter path, and measure

C. Change all keys to random numbers

D. Remove all measures

View Answers
  1. B — A connection between matching columns in related tables.
  2. A — The Products dimension table.
  3. C — Key values, uniqueness, and compatible data types.
  4. A — A relationship used by default for filter propagation.
  5. B — Check the relationship, filter path, and measure.

Summary

In this lesson, you learned how to prepare matching key columns, create relationships through Model view or Manage relationships, and configure relationship properties such as cardinality, cross-filter direction, and active status.

You also explored how to test relationships with report visuals and troubleshoot common issues. Always validate key columns and confirm that filters produce the expected results before relying on the model for analysis.