Welcome back to Virvijay.com, where we simplify Power BI for you! 🎯
In the previous blog, we learned about SUM vs SUMX in DAX. Now, let’s dive into another powerful DAX function: FILTER.
By the end of this blog, you’ll learn:
- ✅ What is FILTER in DAX?
- ✅ How FILTER works with other functions like SUMX
- ✅ Real-world examples for better Power BI analysis
- ✅ Best practices to optimize FILTER for performance
1️⃣ What is FILTER in DAX?
The FILTER function in DAX returns a table that meets specific conditions. It doesn’t calculate values directly but helps filter data dynamically.
📌 Syntax:
DAX
FILTER(<Table>, <Condition>)
🔍 Think of FILTER as a way to extract specific rows from a table before performing calculations.
2️⃣ How FILTER Works with Other DAX Functions
Since FILTER returns a table, it is often used inside other functions like SUMX, AVERAGEX, COUNTX, etc.
📌 Example: Suppose we have a Sales table, and we want to calculate the Total Sales for Orders above ₹50,000.
👉 Using SUMX with FILTER:
DAX
Total Sales Over 50K = SUMX(FILTER(Sales, Sales[Sales Amount] > 50000), Sales[Sales Amount])
✅ What’s happening?
FILTER(Sales, Sales[Sales Amount] > 50000) extracts only sales above ₹50,000.
SUMX then sums only the filtered values.
🚀 Result: Instead of summing all sales, we only sum sales greater than ₹50,000.
3️⃣ Real-Life Examples of FILTER in DAX
Example 1: Calculate Total Sales for a Specific Region
📌 Suppose we have a column Region in our Sales table. We want to calculate total sales only for the "North" region.
👉 DAX Formula:
DAX
Total Sales North = SUMX(FILTER(Sales, Sales[Region] = "North"), Sales[Sales Amount])
🔍 How it works:
- FILTER(Sales, Sales[Region] = "North") → Extracts only "North" region sales
- SUMX → Sums the sales amount of filtered data
Example 2: Count Customers with Sales Above ₹1,00,000
📌 Suppose we want to count how many customers made sales of more than ₹1,00,000.
👉 DAX Formula:
DAX
Customers Over 1L = COUNTROWS(FILTER(Sales, Sales[Sales Amount] > 100000))
🔍 How it works:
- FILTER(Sales, Sales[Sales Amount] > 100000) → Filters sales above ₹1,00,000
- COUNTROWS → Counts the number of filtered rows (customers)
Example 3: Average Sales per Customer for a Specific Product
📌 Suppose we have a column Product Name and want to find the average sales per customer for "Laptop" sales.
👉 DAX Formula:
DAX
Avg Sales Laptop = AVERAGEX(FILTER(Sales, Sales[Product Name] = "Laptop"), Sales[Sales Amount])
🔍 How it works:
- FILTER(Sales, Sales[Product Name] = "Laptop") → Filters only Laptop sales
- AVERAGEX → Finds the average of the filtered sales
🚀 To use FILTER efficiently, follow these best practices:
- ✅ Use FILTER only when necessary. If a simple SUM or SUMX works, avoid unnecessary filters.
- ✅ Combine FILTER with CALCULATE for advanced calculations.
- ✅ Avoid FILTER on large datasets unless optimized properly.
- ✅ Use Variables (VAR) inside FILTER expressions to improve performance.
📌 Example using CALCULATE + FILTER:
DAX
Total High-Value Sales = CALCULATE(SUM(Sales[Sales Amount]), FILTER(Sales, Sales[Sales Amount] > 100000))
🎯 Why use CALCULATE?
CALCULATE modifies the context and makes FILTER more powerful in complex calculations.
5️⃣ Conclusion: When to Use FILTER in Power BI?
- 🚀 Use FILTER when you need to extract specific rows before calculation.
- 🚀 Use FILTER inside SUMX, AVERAGEX, COUNTX for dynamic filtering.
- 🚀 Use CALCULATE + FILTER for advanced DAX calculations.
By mastering FILTER, you’ll improve your Power BI analysis, dashboards, and performance! 🎯
6️⃣ What’s Next?
📌 In the next blog, you’ll learn:
- ✅ CALCULATE in DAX: The Most Powerful Function in Power BI
- ✅ How CALCULATE Works with FILTER
- ✅ Real-life use cases for Business Analysis
🔔 Stay tuned to Virvijay.com for more Power BI tutorials!
📩 For any queries, reach out to support@virvijay.com
💬 Got questions? Drop them in the comments!
