? FILTER Function

? Quick Overview

The FILTER function in DAX is used to apply row-level logical conditions before performing calculations. It is essential when simple column filters are not sufficient.

? Key Concepts

FILTER works row by row
Returns a filtered table
Mostly used inside CALCULATE
Handles complex logical conditions

? Syntax / Theory

? FILTER Syntax

// FILTER basic syntax
FILTER(
TableName,
Condition
)

✨ WHY FILTER FUNCTION IS USED

FILTER is used when you want to apply advanced conditions on data before calculation.

Sales greater than specific value
Multiple condition filtering
Dynamic calculation filtering
Row-level condition checking

CALCULATE = Simple Filter FILTER = Advanced Logical Filter

? Practical Path (Real Steps)

Open Power BI Desktop
Load Data
Go to Report View
Right Click Table → New Measure
Write FILTER DAX

? Practice Sample Data

? Sales Practice Dataset

// Practice dataset for FILTER
City,Category,Sales,Quantity
Pune,Electronics,15000,2
Mumbai,Electronics,25000,1
Delhi,Accessories,5000,5
Pune,Accessories,3000,4
Mumbai,Electronics,40000,2

? How FILTER Thinking Works

Step 1 → Which table → Sales Step 2 → Which condition → Sales > 10000 Step 3 → What result → Sum / Count / Avg

? Example 1 – Sales Greater Than 10000

? View Code Example

// High Sales Calculation
High Sales =
CALCULATE(
SUM(Sales[Sales]),
FILTER(Sales,Sales[Sales] > 10000)
)

Returns total of only rows where Sales > 10000.

? Example 2 – Filter by City (Pune)

? View Code Example

// Pune Sales using FILTER
Pune Sales =
CALCULATE(
SUM(Sales[Sales]),
FILTER(Sales,Sales[City] = "Pune")
)

? If You Want Mumbai Instead

? Change Only This

// Change city value
Sales[City] = "Mumbai"

? Example 3 – Multiple Conditions

? View Code Example

// Mumbai Electronics Sales
Mumbai Electronics =
CALCULATE(
SUM(Sales[Sales]),
FILTER(
Sales,
Sales[City] = "Mumbai" &&
Sales[Category] = "Electronics"
)
)

? Example 4 – Quantity-Based Filter

? View Code Example

// High Quantity Sales
High Qty Sales =
CALCULATE(
SUM(Sales[Sales]),
FILTER(Sales,Sales[Quantity] >= 2)
)

? Example 5 – Dynamic Business Logic

? View Code Example

// Sales between 10000 and 30000
Medium Sales =
CALCULATE(
SUM(Sales[Sales]),
FILTER(
Sales,
Sales[Sales] >= 10000 &&
Sales[Sales] <= 30000
)
)

? Live Output / Explanation

FILTER evaluates each row first and then CALCULATE aggregates only the qualifying rows.

? Use Cases

Conditional KPIs
Advanced dashboard logic
Multi-condition calculations
Business rule enforcement

⚡ FILTER Logic Simulator

Adjust filters below to see how the FILTER() table function slices the data for CALCULATE().

Sales > 10000

Min Quantity: 1

// Current Logic: CALCULATE( SUM(Sales), FILTER(Sales, Sales > 10000 && Qty >= 1) )

Matching Rows
0

Final Sum
$0

✅ Best Practices

Filter Columns, Not Tables: Instead of FILTER(Sales, ...), use FILTER(ALL(Sales[City]), ...) if you only need to filter city. This is much faster on large datasets.
Simple Filters First: Use CALCULATE with simple column filters for basic needs. Only reach for FILTER() when you have complex logic (like comparing two columns).
Keep it Readable: Use && for AND and || for OR conditions inside FILTER to keep your code clean.
Performance Check: FILTER is an iterator. Avoid nesting multiple FILTER functions inside each other as it can slow down your report.

? Try It Yourself

Create Sales > 10000
Create Pune Sales
Change Pune → Mumbai
Create Mumbai Electronics Sales
Create Quantity ≥ 2 Sales