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

Data Modeling Fundamentals

Learn how data modeling works in Power BI, why relationships matter, how fact and dimension tables are organized, and how a well-designed data model supports accurate calculations, interactive reports, and efficient analysis.

1. Introduction to Data Modeling

Data modeling is the process of organizing tables and defining relationships between them. In Power BI, the data model determines how filters and calculations interact across tables in a report.

A good model makes it easier to analyze business questions such as total sales by product, revenue by customer, or monthly performance by region.

Why Data Modeling Is Important

  • Connects data from multiple tables.
  • Supports accurate filtering and aggregation.
  • Reduces unnecessary duplication of descriptive data.
  • Makes DAX measures easier to write and maintain.
  • Improves the organization and usability of reports.
  • Can improve performance when designed appropriately.
Sales OrderID ProductID CustomerID SalesAmount Products ProductID, ProductName Category Customers CustomerID, Name Region Relationships

Visual 1: A sales table connected to product and customer tables.

2. Understand Tables and Their Roles

Tables in a typical analytical model often have two main roles: fact tables and dimension tables. Each serves a different purpose in reporting and analysis.

Fact Tables

A fact table stores business events or measurable transactions. It often contains numeric values and keys that connect each event to descriptive tables.

Examples include sales transactions, payments, orders, and inventory movements.

Dimension Tables

A dimension table stores descriptive information used to categorize, filter, and group facts. Examples include products, customers, dates, and locations.

Feature Fact Table Dimension Table
Purpose Stores business events and measurements. Stores descriptive attributes.
Example Sales Products
Typical columns ProductID, DateKey, Quantity, SalesAmount ProductID, ProductName, Category
Rows Often many rows per product or customer. Usually one row per dimension entity.
Use in reports Provides values to aggregate. Provides fields for grouping and filtering.
Fact Table: Sales OrderID ProductID Quantity SalesAmount Dimension: Products ProductID ProductName Category Brand Fact tables measure activity; dimensions describe the entities involved.

Visual 2: Fact and dimension tables have complementary roles.

3. Understand Keys in a Data Model

Keys identify records and connect related tables. Choosing suitable keys is important because relationships depend on matching values between columns.

Primary Key

A primary key uniquely identifies each row in a table. For example, ProductID can identify a product in a Products table.

Foreign Key

A foreign key references a key in another table. For example, ProductID in the Sales table can reference ProductID in the Products table.

Surrogate Key

A surrogate key is an artificial identifier created to uniquely identify a record. It is useful when source systems do not provide a suitable stable key.

Products ProductID (Primary Key) ProductName Sales ProductID (Foreign Key) SalesAmount Each sales record references a product through ProductID.

Visual 3: A primary key in the dimension table is referenced by a foreign key.

Important: The column on the "one" side of a one-to-many relationship must contain unique values. Duplicate keys in a dimension table can prevent the intended relationship from being created.

4. Understand Relationships

A relationship connects columns in two tables. Power BI uses relationships to propagate filters so that selections in dimension tables can affect related fact-table calculations.

For example, selecting a product category can filter the Products table, which can then filter the Sales table through an appropriate relationship.

Relationship Type Meaning Example
One-to-many (1:*) One row on one side can relate to many rows on the other. One product to many sales records.
One-to-one (1:1) Each row on one side matches at most one row on the other. Two tables storing different attributes for the same unique entity.
Many-to-many (*:*) Rows on both sides may match multiple rows on the other. Some complex shared-membership scenarios.
One-to-Many Relationship Products P101 - Laptop P102 - Monitor 1 : * Sales P101 - Order 501 P101 - Order 502 P102 - Order 503 One product may appear in many sales records.

Visual 4: A product can be associated with multiple sales transactions.

5. Build a Basic Data Model in Power BI

After loading tables into Power BI, use Model view to inspect the tables and create or verify relationships.

Step-by-Step Process

  1. Import the required tables, such as Sales, Products, Customers, and Dates.
  2. Open Model view in Power BI Desktop.
  3. Inspect the columns available in each table.
  4. Identify the key column shared between related tables.
  5. Confirm that the dimension-side key is unique.
  6. Create the relationship by dragging the matching key or using Manage relationships.
  7. Review cardinality and cross-filter direction.
  8. Test the model with a simple table visual and a measure.

Example Model Components

  • Sales: OrderID, ProductID, CustomerID, DateKey, Quantity, SalesAmount.
  • Products: ProductID, ProductName, Category.
  • Customers: CustomerID, CustomerName, Region.
  • Dates: DateKey, Date, Month, Quarter, Year.
Tip: Start with simple one-to-many relationships from dimensions to a fact table. Add more complex relationships only when the business requirement needs them.

6. Basic DAX Measures and the Data Model

DAX measures calculate values using the current filter context. A well-designed model helps measures respond correctly when users select products, dates, or customer regions.

Example: Total Sales

Total Sales = SUM ( Sales[SalesAmount] )

This measure adds the values in SalesAmount for the records visible in the current filter context. If a report user selects a product category, the result can reflect the sales associated with that category through the model relationships.

Example: Total Quantity

Total Quantity = SUM ( Sales[Quantity] )
Note: These measures assume that the Sales table contains numeric columns named SalesAmount and Quantity. Use the actual table and column names from your model.

7. Common Data Modeling Mistakes

Mistake Potential Effect Recommended Practice
Duplicate keys on the dimension side Relationships may fail or require redesign. Validate and deduplicate dimension keys using business rules.
Incorrect data types for relationship keys Matching values may not behave as expected. Use compatible data types.
Unnecessary many-to-many relationships Filtering can become harder to understand. Review the business grain and consider a bridge table when appropriate.
Ambiguous filter paths Results can be confusing or relationships may become inactive. Keep the model simple and review filter direction.
Mixing unrelated data at different grains Aggregations may be misleading. Understand what one row represents in every table.
Duplicating descriptive columns unnecessarily Model size and maintenance complexity can increase. Use suitable dimensions and relationships.

8. Practical Exercise

Exercise: Create a Sales Data Model

Prepare four tables: Sales, Products, Customers, and Dates.

  1. Load all four tables into Power BI Desktop.
  2. Inspect the columns and data types.
  3. Identify the primary key for each dimension table.
  4. Check that dimension keys are unique.
  5. Create relationships from Products to Sales, Customers to Sales, and Dates to Sales.
  6. Review the cardinality and filter direction of each relationship.
  7. Create a Total Sales measure.
  8. Create a report table showing ProductName and Total Sales.
  9. Add Region and Year filters to test how the model responds.
  10. Investigate any unexpected totals or unmatched keys.

Expected outcome: A basic relational model that supports product, customer, and time-based sales analysis.

9. Knowledge Check

Question 1: What is the main purpose of data modeling?

A. To change report colors

B. To organize tables and define relationships for analysis

C. To replace all source data with images

D. To create page navigation only


Question 2: What does a fact table commonly contain?

A. Only report titles

B. Only descriptive text

C. Business events, keys, and measurable values

D. Only visual formatting settings


Question 3: What is a primary key used for?

A. To uniquely identify rows in a table

B. To format a chart

C. To remove every relationship

D. To create a report theme


Question 4: What is a common one-to-many relationship?

A. One product associated with many sales records

B. One report title associated with one color

C. One chart associated with many pages only

D. One measure associated with one font


Question 5: What does a DAX SUM measure do?

A. Sorts text alphabetically

B. Adds numeric values within the current filter context

C. Deletes duplicate tables automatically

D. Creates a relationship without keys

View Answers
  1. B — Organizes tables and relationships for analysis.
  2. C — Stores business events, keys, and measurable values.
  3. A — Uniquely identifies rows in a table.
  4. A — One product can be associated with many sales records.
  5. B — Adds numeric values within the current filter context.

Summary

In this lesson, you learned the fundamentals of data modeling, the roles of fact and dimension tables, and how primary and foreign keys connect related data.

You also explored relationship types, Model view, basic DAX measures, and common modeling mistakes. A clear and well-tested data model supports reliable filtering, accurate calculations, and effective Power BI reports.