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

Cardinality and Filter Direction in Power BI

Relationships connect tables in a Power BI data model. Two important relationship properties are cardinality and cross-filter direction. Cardinality defines how values match between tables, while filter direction defines how filters travel across a relationship.

Understanding these properties helps you build accurate reports, prevent unexpected totals, and create models that are easier to maintain.

Learning Objectives

  • Understand one-to-many, one-to-one, and many-to-many relationships.
  • Identify the one side and the many side of a relationship.
  • Understand single-direction and both-direction filtering.
  • Choose suitable relationship settings for common reporting scenarios.
  • Recognize common relationship problems and troubleshooting methods.

1. What Is Cardinality?

Cardinality describes how rows in one table correspond to rows in another table through related column values. The correct cardinality depends on the uniqueness of the relationship columns and the business meaning of the data.

One-to-Many One 1 : * Many One customer can have many orders. One-to-One One 1 : 1 One Each key matches at most one row. Many-to-Many Many * : * Many A key can repeat on both sides.
Diagram 1: The three common cardinality patterns used when modeling related tables.

2. One-to-Many Relationship (1:*)

A one-to-many relationship is common in Power BI. The key column on the one side contains unique values, while the related column on the many side can contain repeated values.

Example: Customers and Orders

Customers Table Orders Table
CustomerID: 101 — Asha OrderID: 5001 — CustomerID: 101
CustomerID: 102 — Ravi OrderID: 5002 — CustomerID: 101
CustomerID: 103 — Neha OrderID: 5003 — CustomerID: 102

CustomerID 101 appears once in the Customers table but twice in the Orders table. This supports a one-to-many relationship from Customers to Orders.

Customers CustomerID Name 101 Asha 102 Ravi 103 Neha Orders OrderID CustomerID 5001 101 5002 101 5003 102 1 : * A customer can be associated with multiple orders.
Diagram 2: A unique customer key relates to multiple order records.
Best Practice: In a typical sales model, place Customers on the one side and Orders or Sales on the many side. Use the unique customer key to create the relationship.

3. One-to-One Relationship (1:1)

A one-to-one relationship means each matching key occurs no more than once in each table. It can be useful when information about the same entity is split into two tables, such as employee details and employee security settings.

Example

Employee Table EmployeeSettings Table
EmployeeID: 201 — Sam EmployeeID: 201 — English
EmployeeID: 202 — Priya EmployeeID: 202 — Marathi

Each EmployeeID occurs once in each table. If the data has this structure and the relationship is appropriate for the model, a one-to-one relationship may be suitable.

Important: Do not choose one-to-one merely because it seems simpler. Verify that both relationship columns are unique and confirm that the tables represent the intended business relationship. If one table contains repeated keys, one-to-one is not appropriate.

4. Many-to-Many Relationship (*:*)

A many-to-many relationship occurs when the relationship key can repeat in both tables. For example, students can enroll in multiple courses, and each course can contain multiple students.

Students StudentID Name 1 Asha 2 Ravi 3 Neha Courses CourseID Title C01 Excel C02 Power BI C03 SQL * : * Students can take many courses; courses can have many students.
Diagram 3: Many-to-many relationships allow repeated key values on both sides.

Many-to-many relationships can be valid, but they require careful modeling. In many business models, a bridge table provides a clearer structure and makes the relationship logic easier to understand.

Using a Bridge Table

Students Enrollment Courses
StudentID: 1 StudentID: 1, CourseID: C01 CourseID: C01
StudentID: 2 StudentID: 1, CourseID: C02 CourseID: C02
StudentID: 3 StudentID: 2, CourseID: C01 CourseID: C03

The Enrollment table records which student takes which course. A common design uses one-to-many relationships from Students to Enrollment and from Courses to Enrollment.

5. What Is Cross-Filter Direction?

Cross-filter direction determines how a filter from one related table affects rows and calculations in another table. In a simple star schema, filters commonly flow from dimension tables toward fact tables.

Product ProductID ProductName Category Sales ProductID Quantity SalesAmount Single Direction Filter flows → A Product category selection filters related Sales rows.
Diagram 4: With single-direction filtering, a dimension selection filters the related fact table.

6. Single Direction vs Both Directions

Setting How It Works Typical Consideration
Single Filters flow in one configured direction across the relationship. Often the preferred starting point for a star schema.
Both Filters can flow in both directions across the relationship. Use only when the model requires it and the behavior is understood.

In a typical star schema, a Product dimension filters Sales, and a Customer dimension filters Sales. The fact table generally does not need to filter the dimension table for ordinary reporting.

Single Direction Dimension Table Fact Table One configured direction Simple and predictable for common star schemas Both Directions Dimension Table Fact Table Filters flow both ways Can support specific scenarios but may add ambiguity
Diagram 5: Both-direction filtering can be useful, but it should be configured deliberately.
Use Both with Care: Bidirectional filtering can create ambiguous filter paths, unexpected results, or harder-to-debug measures when multiple relationships connect the same tables. Do not enable it on every relationship by default.

7. How to Set Cardinality and Filter Direction

  1. Open your report in Power BI Desktop.
  2. Select Model view from the left navigation.
  3. Select an existing relationship line between two tables, or create a relationship by dragging a matching key column to another table.
  4. Open the relationship properties by double-clicking the relationship line or using the relationship editing option.
  5. Verify the columns selected for the relationship.
  6. Choose the appropriate cardinality based on the actual uniqueness of the columns.
  7. Set the cross-filter direction based on how filters should travel through the model.
  8. Confirm the settings and test the report with a table visual and slicers.

Before Saving a Relationship

  • Is the key on the one side unique?
  • Do the key columns use compatible data types?
  • Do related values match as expected?
  • Are blank or unmatched keys present?
  • Is the selected filter direction necessary?
  • Could another active relationship create an ambiguous filter path?

8. Common Problems and Solutions

Problem Possible Cause Suggested Check
Power BI cannot create a one-to-many relationship. The supposed one-side key contains duplicates or the selected columns are unsuitable. Check uniqueness, blanks, and the intended business key.
A slicer does not filter a visual as expected. The relationship is missing, inactive, or its filter direction does not support the intended path. Inspect the relationship and test the filter path.
A total looks unexpectedly high. Duplicate dimension keys, an unsuitable many-to-many relationship, or incorrect data granularity. Validate keys, row counts, and the table grain.
Power BI reports an ambiguous relationship path. Multiple active paths may allow filters to travel between tables. Review active relationships and simplify the model where possible.
Some fact rows do not appear under a dimension category. Some foreign keys may not match a dimension key. Compare distinct key values and identify unmatched records.

9. Practical Lab: Configure a Sales Model

Use the following tables to practice relationship settings in Power BI Desktop.

Products Sales
ProductID: P01, Product: Laptop SaleID: 1, ProductID: P01, Amount: 50000
ProductID: P02, Product: Mouse SaleID: 2, ProductID: P01, Amount: 52000
ProductID: P03, Product: Keyboard SaleID: 3, ProductID: P02, Amount: 1500
SaleID: 4, ProductID: P03, Amount: 2500

Tasks

  1. Load both tables into Power BI Desktop.
  2. Create a relationship using ProductID.
  3. Set Products as the one side and Sales as the many side.
  4. Use single-direction filtering as the initial configuration.
  5. Create a table visual containing Product and Sales Amount.
  6. Add a Product slicer and test whether it filters the sales values.
  7. Record the total sales amount and check it against the source data.
Expected Result: The total sales amount is 106,000. The Laptop category should total 102,000, Mouse should total 1,500, and Keyboard should total 2,500.

10. Knowledge Check

Question 1

In a typical Customers-to-Orders relationship, which table is usually on the one side?

Answer: Customers, provided CustomerID is unique in that table.

Question 2

What does a many-to-many relationship mean?

Answer: The relationship key can repeat on both sides.

Question 3

What is the common starting filter direction for a star schema?

Answer: Single direction from dimension tables toward fact tables.

Question 4

Why should Both-direction filtering be used carefully?

Answer: It can introduce ambiguous filter paths or unexpected results in a complex model.

Question 5

What should you check if Power BI cannot use a column as the one side of a relationship?

Answer: Check whether its values are unique and whether the selected key is appropriate.

Summary

  • Cardinality describes how rows relate across two tables.
  • One-to-many (1:*) is common for dimension-to-fact relationships.
  • One-to-one (1:1) requires unique keys on both sides.
  • Many-to-many (*:*) requires careful consideration of model design and filter behavior.
  • Single-direction filtering is a common starting point for a star schema.
  • Both-direction filtering should be enabled only when the model requires it.
  • Always test relationships using slicers, visuals, and source-data totals.