Welcome back to Virvijay.com, where we simplify Power BI for everyone! 🎯
In our previous blog, we explored RANKX to create dynamic leaderboards. Today, we’ll focus on two of the most powerful DAX functions: FILTER and ALLSELECTED.
By the end of this blog, you’ll learn how to:
- ✔ Use FILTER for row-level filtering in DAX
- ✔ Use ALLSELECTED to work with selected filters
- ✔ Optimize your Power BI reports with these functions
1️⃣ What is the FILTER Function in Power BI?
The FILTER function allows us to apply conditions to a table and return a filtered version of it.
📌 Why use FILTER?
- ✔ Helps in applying complex conditions
- ✔ Filters data dynamically without affecting visuals
- ✔ Works well with other DAX functions
FILTER Syntax:
DAX
FILTER(<Table>, <Condition>)
- ✔ <Table> → The table to filter (e.g., Sales Table)
- ✔ <Condition> → The filtering condition
Example: Filter Sales Above ₹50,000
DAX
High Sales = FILTER(Sales, Sales[Amount] > 50000)
🚀 How it works:
- ✔ Returns only rows where Sales[Amount] is greater than ₹50,000
- ✔ Can be used inside CALCULATE, SUMX, and other DAX functions
✅ Best Use Cases:
- ✔ Filtering high-value transactions
- ✔ Finding customers with large purchases
2️⃣ Advanced FILTER Usage: Combining Multiple Conditions
We can use FILTER with multiple conditions using the && (AND) and || (OR) operators.
👉 Example: Find Sales Above ₹50,000 in 2024
DAX
High Sales 2024 = FILTER(Sales, Sales[Amount] > 50000 && Sales[Year] = 2024)
🚀 How it works:
- ✔ Only includes sales above ₹50,000
- ✔ Filters only for the year 2024
✅ Best Use Cases:
- ✔ Finding high-value sales in specific years
- ✔ Filtering data for custom date ranges
3️⃣ What is the ALLSELECTED Function in Power BI?
The ALLSELECTED function keeps only the selected filters in a visual while ignoring other external filters.
📌 Why use ALLSELECTED?
- ✔ Helps in dynamic comparisons
- ✔ Works well with percentage calculations
- ✔ Keeps user-applied filters intact
ALLSELECTED Syntax:
DAX
ALLSELECTED(<Table>)
- ✔ <Table> → The table or column to keep selected filters for
Example: Calculate % of Total Sales
DAX
% of Total Sales =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Sales)))
🚀 How it works:
- ✔ [Total Sales] calculates normal sales
- ✔ ALLSELECTED(Sales) ensures only selected filters apply
✅ Best Use Cases:
- ✔ Creating % of total sales calculations
- ✔ Comparing values within selected filters
4️⃣ Combining FILTER and ALLSELECTED for Dynamic Calculations
We can combine both FILTER and ALLSELECTED for advanced analysis.
👉 Example: Find High-Value Sales in a Selected Region
DAX
High Sales in Region =
CALCULATE([Total Sales],
FILTER(ALLSELECTED(Sales), Sales[Amount] > 50000)
)
🚀 How it works:
- ✔ FILTER(ALLSELECTED(Sales), Sales[Amount] > 50000) → Filters only within the selected region
- ✔ CALCULATE([Total Sales]) → Calculates total sales after filtering
✅ Best Use Cases:
- ✔ Dynamic filtering within user-selected values
- ✔ Creating customized regional reports
5️⃣ Comparing ALL vs. ALLSELECTED
🚀 Example:
- ✔ ALL(Sales) → Ignores all filters, returning the entire dataset
- ✔ ALLSELECTED(Sales) → Keeps only selected filters (e.g., Region, Category)
✅ When to Use?
- ✔ Use ALL() when you want a global calculation
- ✔ Use ALLSELECTED() when you want to respect user selections
6️⃣ Best Practices for Using FILTER and ALLSELECTED in Power BI
✅ Use FILTER for Complex Conditions
- ✔ Helps in advanced row-level filtering
✅ Use ALLSELECTED for Dynamic Comparisons
- ✔ Works well for % of total calculations
✅ Optimize Performance
- ✔ Avoid using FILTER on large datasets inside calculated columns
✅ Test with Different Filters
- ✔ Always test ALLSELECTED in different slicer settings
7️⃣ Conclusion: Why FILTER and ALLSELECTED Matter in Power BI?
🚀 FILTER and ALLSELECTED are essential for precise, dynamic Power BI reports!
8️⃣ What’s Next?
📌 In the next blog, we’ll cover:
- ✅ Advanced DAX Performance Optimization
- ✅ Understanding Variables (VAR) in Power BI
- ✅ Creating Dynamic KPIs with DAX
🔔 Stay tuned to Virvijay.com for more Power BI tutorials!
📩 For any queries, reach out to support@virvijay.co
💬 Got questions? Drop them in the comments!
