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

Row Context and Filter Context

Row context and filter context are two fundamental concepts in DAX. They determine how expressions evaluate data and explain why the same formula can produce different results in different situations.

Understanding these contexts helps you create accurate calculated columns, measures, totals, conditional calculations, and business performance indicators in Power BI.

Learning Objectives

  • Understand row context and how it evaluates individual rows.
  • Understand filter context created by report selections and DAX.
  • Identify differences between calculated columns and measures.
  • Understand how CALCULATE modifies filter context.
  • Recognize how iterator functions create row context.
  • Practise writing DAX expressions using both contexts.

1. What Is Row Context?

Row context means that a DAX expression is evaluated for a particular row. Calculated columns naturally have row context because Power BI evaluates their formulas for each row of the table.

For example, a Sales table may contain Quantity and UnitPrice columns. A calculated column can multiply these values for each individual row.

SalesAmount =
Sales[Quantity] * Sales[UnitPrice]

For every row, DAX reads that row's Quantity and UnitPrice values and calculates its SalesAmount.

Row Context Current Row Quantity = 2 UnitPrice = 50000 Product = Laptop DAX Formula 2 × 50000 Evaluate this row Calculated Value 100000 SalesAmount

2. What Is Filter Context?

Filter context determines which rows are included when a calculation is evaluated. It can be created by slicers, visual fields, page filters, report filters, relationships, and DAX expressions.

For example, a Total Sales measure may show sales for all products. When a user selects Laptop in a Product slicer, the same measure calculates sales for the selected product.

Total Sales =
SUM(Sales[SalesAmount])
Filter Context Product Slicer Selected: Laptop Report filter Total Sales SUM(SalesAmount) Evaluated in context Result Laptop sales Selected rows only The measure responds to the active filter context.

3. Row Context vs. Filter Context

Feature Row Context Filter Context
Meaning Identifies the current row for expression evaluation. Determines which rows participate in a calculation.
Common source Calculated columns and iterator functions. Slicers, visuals, report filters, relationships, and CALCULATE.
Typical use Calculate quantity multiplied by unit price for each row. Calculate sales for a selected product or date range.
Example Sales[Quantity] * Sales[UnitPrice] SUM(Sales[SalesAmount]) under a product filter.
Two DAX Contexts Row Context • Evaluates the current row. • Common in calculated columns. Filter Context • Determines included rows. • Common in measures. Both contexts can affect DAX results.

4. Calculated Columns and Row Context

A calculated column is evaluated row by row. For example, create a SalesAmount column using Quantity and UnitPrice.

SalesAmount =
Sales[Quantity] * Sales[UnitPrice]

If Quantity is 3 and UnitPrice is 1200 for a particular row, the calculated SalesAmount is 3600 for that row.

The result is stored in the model and can be used in table visuals, slicers, filters, and other calculations where a column is appropriate.

5. Measures and Filter Context

A measure is evaluated under the filter context of the visual or query. Create the following measure to calculate total sales.

Total Sales =
SUM(Sales[SalesAmount])

Add Product and Total Sales to a table visual. Power BI evaluates Total Sales separately for each product because each table row establishes a different filter context.

6. How CALCULATE Changes Filter Context

CALCULATE evaluates an expression under a modified filter context. It can add filters or modify existing filters according to the expression and model.

Electronics Sales =
CALCULATE(
    [Total Sales],
    Sales[Category] = "Electronics"
)

This measure applies a filter for the Electronics category while evaluating Total Sales. The example assumes that the Sales table contains a Category column.

Remember: CALCULATE is one of the key functions for working with filter context. It does not simply mean "add a filter"; its filter arguments can replace existing filters on the same column unless a function such as KEEPFILTERS changes that behavior.

7. How Iterator Functions Create Row Context

Iterator functions such as SUMX and AVERAGEX evaluate an expression for each row of their input table. This evaluation creates row context within the iterator.

Total Revenue =
SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

SUMX evaluates Quantity multiplied by UnitPrice for each row and then adds the results. This is useful when revenue is not already stored as a column in the table.

SUMX and Row Context Sales Table Each input row Row Expression Quantity × UnitPrice SUMX Result Sum of row values The iterator creates row context for its expression.

8. Understanding Context Transition

Context transition occurs when CALCULATE or CALCULATETABLE converts an existing row context into filter context. This is important when calculations combine iterators and measures.

For example, when a measure is evaluated inside an iterator, the measure's implicit use of CALCULATE causes context transition. The current row can then influence the measure evaluation through the resulting filter context.

Sales Total by Product =
SUMX(
    VALUES(Product[ProductName]),
    [Total Sales]
)

This example iterates over the distinct product names and evaluates Total Sales for each product. It assumes a Product table related appropriately to the Sales table.

Important: Context transition is an advanced DAX concept. Make sure you understand relationships, row context, and filter context before using complex iterator expressions.

9. Practical Comparison

Business Requirement Recommended Approach Reason
Calculate revenue for each transaction row. Calculated column or SUMX measure, depending on the model. Requires a row-level expression.
Display total sales by product. Measure using SUM. The result should respond to visual filters.
Calculate sales for a particular category. CALCULATE with a category filter. Changes the filter context for the calculation.
Calculate an expression for each row and add results. SUMX. Evaluates a row-level expression before aggregation.
Show a dynamic total controlled by a slicer. Measure. Responds to the active filter context.

10. Common Mistakes

Mistake 1: Assuming a measure has automatic row context

Measures are evaluated in filter context. To calculate row-level expressions across a table, use an appropriate iterator such as SUMX.

Mistake 2: Confusing a calculated column with a measure

Calculated columns store row-level results, while measures calculate results dynamically for the current context.

Mistake 3: Ignoring relationships

Filters propagate through model relationships. Incorrect or inactive relationships can cause unexpected calculation results.

Mistake 4: Misunderstanding CALCULATE

CALCULATE modifies filter context. Its behavior depends on existing filters and the filter arguments supplied.

11. Student Practice Tasks

Task 1: Create a calculated column for SalesAmount using Quantity and UnitPrice.

Task 2: Create a Total Sales measure using SUM.

Task 3: Add Product and Total Sales to a table visual.

Task 4: Add a Product slicer and observe how Total Sales changes.

Task 5: Create an Electronics Sales measure using CALCULATE.

Task 6: Create a Total Revenue measure using SUMX.

Task 7: Explain the difference between row context and filter context using your own example.

Task 8: Describe context transition and identify a situation in which it occurs.

12. Knowledge Check

Question 1: What is row context?

Answer: The context in which an expression is evaluated for a particular row.

Question 2: What is filter context?

Answer: The set of filters determining which rows participate in a calculation.

Question 3: Which function modifies filter context?

Answer: CALCULATE.

Question 4: Which function can iterate over a table and sum an expression?

Answer: SUMX.

Question 5: What is context transition?

Answer: The conversion of row context into filter context by CALCULATE or CALCULATETABLE.

Summary

In this lesson, you learned the difference between row context and filter context in DAX. Row context evaluates an expression for a particular row, while filter context determines which rows are included in a calculation.

You also practised calculated columns, measures, CALCULATE, and SUMX. Finally, you explored context transition and learned how relationships and report filters affect calculation results. Always verify the model relationships and the intended evaluation context when troubleshooting DAX formulas.