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

DAX Filter and Iterator Functions

CIIT TRANING INSTITUTE | Power BI Course

DAX filter and iterator functions help analyze data according to specific conditions and perform calculations row by row. These functions are useful for calculating filtered sales, product revenue, average order values, and other business metrics in Power BI.

Learning Objectives

  • Understand how CALCULATE changes filter context.
  • Use FILTER to return rows that satisfy a condition.
  • Use SUMX and AVERAGEX for row-by-row calculations.
  • Understand the difference between row context and filter context.
  • Combine filter and iterator functions in practical measures.

1. Sample Sales Data

The following examples use a table named Sales. Create the sample table in Power BI or adapt the formulas to your existing dataset.

Product Category Quantity Unit Price
Laptop Electronics 2 50000
Mouse Accessories 5 500
Keyboard Accessories 3 1200
Monitor Electronics 2 12000

For these examples, assume the table contains numeric columns named Quantity and UnitPrice.

2. Understanding Filter Context

Filter context determines which rows are included in a calculation. Filters can come from report slicers, visual fields, page filters, relationships, or DAX expressions.

Filter Context Report Filters Slicers and visuals DAX Calculation CALCULATE or measure Result Filtered calculation The same measure can return different results under different filters.

3. CALCULATE Function

CALCULATE evaluates an expression in a modified filter context. It is one of the most important DAX functions because it lets you calculate measures under specific conditions.

Syntax

CALCULATE(
    <expression>,
    <filter1>,
    <filter2>
)

Example: Electronics Sales

Electronics Sales =
CALCULATE(
    SUMX(
        Sales,
        Sales[Quantity] * Sales[UnitPrice]
    ),
    Sales[Category] = "Electronics"
)

This measure calculates revenue for rows in the Electronics category. The category condition changes the filter context used by CALCULATE.

Remember: CALCULATE changes the filter context of an expression. A simple Boolean filter such as Sales[Category] = "Electronics" is suitable when you need to filter by a straightforward column condition.

4. FILTER Function

FILTER evaluates a condition for each row of a table and returns a table containing only the rows that satisfy the condition. It is commonly used when the filtering logic needs a more detailed expression.

Syntax

FILTER(
    <table>,
    <condition>
)

Example: Rows with Quantity Greater Than Two

Sales for Quantity Above Two =
CALCULATE(
    SUMX(
        Sales,
        Sales[Quantity] * Sales[UnitPrice]
    ),
    FILTER(
        Sales,
        Sales[Quantity] > 2
    )
)

FILTER checks the Quantity value in each row and returns rows where Quantity is greater than two. CALCULATE then evaluates the revenue expression using that filtered table.

FILTER: Quantity Greater Than Two Original Rows Laptop — Quantity 2 Excluded Mouse — Quantity 5 Included Keyboard — Quantity 3 Included Filtered Table Mouse — Quantity 5 Keyboard — Quantity 3 Illustration shows selected rows; actual results depend on the data.

5. Iterator Functions

Iterator functions evaluate an expression for each row of a table and then combine the results. Many iterator functions end with the letter X.

Function Purpose Example Use
SUMX() Evaluates an expression per row and sums the results. Quantity multiplied by unit price.
AVERAGEX() Evaluates an expression per row and averages the results. Average revenue per product row.
COUNTX() Counts results of an expression that are not blank. Counting nonblank calculated results.
MAXX() Returns the largest result from a row-by-row expression. Finding the largest row-level revenue.
MINX() Returns the smallest result from a row-by-row expression. Finding the smallest row-level revenue.

6. SUMX Function

SUMX calculates an expression for every row in a table and then adds those calculated values together.

Syntax

SUMX(
    <table>,
    <expression>
)

Example: Calculate Total Revenue

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

SUMX multiplies Quantity by UnitPrice for each row and adds the row results together.

Product Quantity Unit Price Row Revenue
Laptop 2 50000 100000
Mouse 5 500 2500
Keyboard 3 1200 3600
Monitor 2 12000 24000

Total Revenue = 100000 + 2500 + 3600 + 24000 = 130100.

How SUMX Works Quantity × Unit Price Each Row Revenue SUMX Adds all row results Calculate first, then aggregate.

7. AVERAGEX Function

AVERAGEX evaluates an expression for each row and returns the average of the resulting values.

Average Row Revenue =
AVERAGEX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

Using the sample data, the four row revenues are 100000, 2500, 3600, and 24000. Their average is 32525.

Important Difference: AVERAGEX averages the results of an expression evaluated for each row. It is not always the same as dividing total revenue by total quantity or averaging a single column. Choose the formula that matches the business question.

8. MAXX and MINX Functions

MAXX returns the largest value produced by an expression evaluated across a table. MINX returns the smallest value.

Highest Row Revenue =
MAXX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)
Lowest Row Revenue =
MINX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

In the sample dataset, the highest row revenue is 100000 and the lowest row revenue is 2500.

9. Row Context vs. Filter Context

Row context refers to the current row being evaluated, such as the current row inside SUMX. Filter context determines which rows are available to a calculation, such as the rows selected by a report slicer.

Two Important DAX Contexts Row Context • Evaluates the current row. • Common in iterator expressions. • Example: Quantity × UnitPrice. Filter Context • Determines included rows. • Comes from visuals and filters. • Modified by CALCULATE. Understanding both contexts helps explain many DAX results.

10. Combine CALCULATE, FILTER, and SUMX

The following measure calculates revenue for rows where the unit price is greater than 1000.

Revenue for Premium Products =
CALCULATE(
    SUMX(
        Sales,
        Sales[Quantity] * Sales[UnitPrice]
    ),
    FILTER(
        Sales,
        Sales[UnitPrice] > 1000
    )
)

FILTER selects qualifying rows, SUMX calculates revenue for each selected row, and CALCULATE evaluates the expression under the resulting filter context.

Performance Tip: For simple conditions on a single column, a direct Boolean filter in CALCULATE is often simpler and can be more efficient than wrapping the condition in FILTER. Use FILTER when its table expression or row-level condition is needed.

11. Common Mistakes

Mistake Why It Matters Better Approach
Using SUM for Quantity × UnitPrice. SUM aggregates one column and does not multiply two columns row by row. Use SUMX for the row-level expression.
Using FILTER for every condition. It can make expressions more complex than necessary. Use a direct CALCULATE filter for simple column conditions.
Confusing row context with filter context. The calculation may return unexpected results. Identify which rows are being iterated and which rows are filtered.
Assuming AVERAGEX equals average quantity. It averages the expression results, not automatically a particular column. Write the expression to match the intended metric.

12. Student Practice Tasks

Task 1: Create a Total Revenue measure using SUMX.

Task 2: Calculate revenue for Electronics products using CALCULATE.

Task 3: Use FILTER to select sales rows with Quantity greater than two.

Task 4: Create Average Row Revenue using AVERAGEX.

Task 5: Find the highest and lowest row revenues using MAXX and MINX.

Task 6: Create a measure that calculates revenue for products with a UnitPrice greater than 1000.

Task 7: Explain the difference between row context and filter context in your own words.

13. Knowledge Check

Question 1: Which function modifies filter context?

Answer: CALCULATE.

Question 2: Which function returns a table containing rows that satisfy a condition?

Answer: FILTER.

Question 3: Which function calculates an expression row by row and sums the results?

Answer: SUMX.

Question 4: What does AVERAGEX return?

Answer: The average of the expression results evaluated over the input table.

Question 5: What is row context?

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

Summary

  • CALCULATE evaluates an expression in a modified filter context.
  • FILTER returns rows that satisfy a specified condition.
  • SUMX evaluates an expression row by row and sums the results.
  • AVERAGEX calculates the average of row-level expression results.
  • MAXX and MINX find the maximum and minimum expression results across a table.
  • Row context and filter context serve different purposes in DAX.
  • Choose direct filters for simple conditions and use FILTER when more complex table filtering is required.