Description
Returns a table that represents a subset of another table or expression.
Return a Table
Yes
Status
Done
‣
1. Function Overview
‣
2. Using FILTER() — Single & Multiple Column Filters
‣
3. CONTOSO PRACTICAL EXAMPLE
4. FILTER() vs CALCULATE()
You can rewrite the previous example using CALCULATE() — it’s more efficient because it applies the filter context directly.
Efficient Version
Non US Sales (Efficient) :=
CALCULATE(
SUM('Sales'[SalesAmount]),
'Geography'[CountryRegionName] <> "United States"
)
✅ Same output, faster performance.
When to Use FILTER() Instead
Use FILTER() when:
- You need complex logic (e.g., multiple AND/OR conditions).
- You want to filter based on aggregated values.
- You must nest multiple functions or apply dynamic filters.
Example:
FILTER(
'Sales',
'Sales'[Quantity] > 10 &&
RELATED('Geography'[Region]) = "Europe"
)
5. Key Exam Insights (PL-300)
Concept | What to Remember |
FILTER() returns a table | It never returns a scalar value. |
Usage | Always used inside another function like SUMX() or CALCULATE(). |
Row Context | Evaluates each row and retains only those that return TRUE. |
Performance | Avoid for simple filters — CALCULATE() handles them faster. |
PL-300 Tip | Expect to identify when FILTER() is necessary — e.g., when combining OR conditions, referencing related tables, or filtering on aggregated expressions. |
6. Common Practice Exercises
Task | Formula |
1️⃣ Get sales where amount > £500 | SUMX(FILTER('Sales', 'Sales'[SalesAmount] > 500), 'Sales'[SalesAmount]) |
2️⃣ Get sales for 2020 only | SUMX(FILTER('Sales', YEAR('Sales'[OrderDate]) = 2020), 'Sales'[SalesAmount]) |
3️⃣ Get average discount for France | AVERAGEX(FILTER('Sales', RELATED('Geography'[CountryRegionName]) = "France"), 'Sales'[DiscountAmount]) |
4️⃣ Count rows where quantity > 10 | COUNTROWS(FILTER('Sales', 'Sales'[Quantity] > 10)) |
5️⃣ Get sales where profit margin > 0.3 | SUMX(FILTER('Sales', 'Sales'[ProfitMargin] > 0.3), 'Sales'[SalesAmount]) |
7. Summary
Concept | Description |
Purpose | Returns a subset of a table meeting specific conditions. |
Return Type | Table |
Used Inside | CALCULATE(), SUMX(), AVERAGEX(), COUNTROWS() |
Best Use Case | Complex filtering with multiple conditions or nested logic. |
Contoso Example | FILTER('Sales', RELATED('Geography'[CountryRegionName]) <> "United States") removes US sales. |
Tip | Use CALCULATE for simple filters; FILTER for complex conditions or computed expressions. |