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.
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.
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.
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.
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.
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.
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.
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.