When working in Power BI or Power Pivot, one of the most important concepts you’ll come across is filtering data. Filters allow you to control what data you want to see and what data you want to ignore.
At first glance, you may think filtering is just like applying a filter in Excel — tick marks and drop-downs. But in DAX (Data Analysis Expressions), filter functions are much more powerful. They let you dynamically control the filter context, which decides what numbers are being calculated behind the scenes.
If you’re learning DAX for Data Analysis, MIS reporting, or preparing for interviews, then Filter Functions are a must-know.
In this article, we’ll explore the most important filter functions in DAX with real-world examples, datasets, and practical use cases.
1. Why Filtering is Important in DAX
In DAX, every calculation happens inside a context:
- Row context – works row by row in a table.
- Filter context – controls which rows are included in the calculation.
Filter functions allow us to modify this filter context. This is extremely useful when you want to:
- Calculate % of total (ignoring filters).
- Get data for selected categories only.
- Remove filters from one column but keep others.
- Create dynamic measures based on user selections.
2. List of Key Filter Functions in DAX
Here are the main filter functions we will cover in detail:
- FILTER()
- ALL()
- ALLEXCEPT()
- ALLSELECTED()
- REMOVEFILTERS()
- VALUES()
- DISTINCT()
- RELATEDTABLE()
Also Read: Basic DAX Functions
3. FILTER() Function
Syntax:
FILTER (table, condition)
This function returns a filtered table based on conditions you define.
Example 1: Get Employees with Salary Greater than 50,000
Dataset (Employees Table):
| EmpID | Name | Dept | Salary |
|---|---|---|---|
| 101 | Ramesh | HR | 40,000 |
| 102 | Priya | Sales | 65,000 |
| 103 | Ankit | IT | 55,000 |
| 104 | Deepa | HR | 48,000 |
| 105 | Suresh | Sales | 72,000 |
DAX:
High Salary Employees = FILTER(Employees, Employees[Salary] > 50000)
Result: Priya, Ankit, Suresh
Example 2: Get Sales Only for Year 2024
Sales2024 = FILTER(Sales, Sales[Year] = 2024)
Read More: Iterator Functions in DAX
4. ALL() Function
ALL removes filters from columns or tables.
Syntax:
ALL(TableOrColumn)Example 1: Calculate % of Total Sales
Suppose your Sales table looks like this:
| Product | Region | Sales |
|---|---|---|
| A | East | 200 |
| A | West | 300 |
| B | East | 150 |
| B | West | 250 |
DAX:
% of Total Sales =
DIVIDE(
SUM(Sales[Sales]),
CALCULATE(SUM(Sales[Sales]), ALL(Sales))
)
Example 2: Ranking Ignoring Filters
Product Rank = RANKX(ALL(Sales[Product]), SUM(Sales[Sales]))
5. ALLEXCEPT() Function
This function removes all filters except the ones you keep.
Syntax:
ALLEXCEPT(Table, Column1, Column2, …)
Example 1: Total Sales by Region, Ignoring Product
Sales by Region = CALCULATE(SUM(Sales[Sales]), ALLEXCEPT(Sales, Sales[Region]))
Example 2: Year-wise Sales but Ignore Other Filters
Year Sales = CALCULATE(SUM(Sales[Sales]), ALLEXCEPT(Sales, Sales[Year]))
6. ALLSELECTED() Function
ALLSELECTED respects the filters applied by the user in visuals.
Syntax:
ALLSELECTED(TableOrColumn)Example 1: % Contribution of Selected Products
% Selected Sales =
DIVIDE(
SUM(Sales[Sales]),
CALCULATE(SUM(Sales[Sales]), ALLSELECTED(Sales[Product]))
)
Example 2: Cumulative Sales for Selected Months
Cumulative Sales =
CALCULATE(
SUM(Sales[Sales]),
FILTER(ALLSELECTED(Sales[Month]), Sales[Month] <= MAX(Sales[Month]))
)
7. REMOVEFILTERS() Function
Works similar to ALL, but is more flexible.
Syntax:
REMOVEFILTERS(TableOrColumn)Example 1: Remove Region Filter Only
Sales Without Region Filter = CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Sales[Region]))
Example 2: Compare With and Without Filters
You can create two measures:
With Filters = SUM(Sales[Sales])
Without Filters = CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Sales))
8. VALUES() and DISTINCT()
Both return unique values from a column, but:
- VALUES keeps blank values
- DISTINCT removes blank values
Syntax:
VALUES(Column)
DISTINCT(Column)
Example 1: Count Distinct Customers
Customer Count = COUNTROWS(VALUES(Sales[CustomerID]))
Example 2: Show All Product Categories
Categories = CONCATENATEX(DISTINCT(Sales[Product]), Sales[Product], ", ")
9. RELATEDTABLE() Function
This function returns related rows from another table.
Syntax:
RELATEDTABLE(RelatedTableName)Example 1: Show All Orders Related to Selected Customer
Orders for Customer = COUNTROWS(RELATEDTABLE(Orders))
Example 2: Count Employees Under Each Manager
Team Size = COUNTROWS(RELATEDTABLE(Employees))
10. Real-Life MIS / Data Analyst Scenarios
- Year-to-Date Reports: Use FILTER with CALCULATE to get sales from Jan to current month.
- Customer Segmentation: Use DISTINCT + FILTER to get unique customers in each segment.
- Regional Analysis: Use ALLEXCEPT to compare sales region-wise while ignoring product-level filters.
11. Limitations and Best Practices
- Don’t overuse FILTER inside CALCULATE on very large datasets → it can slow down reports.
- Use ALL carefully — sometimes removing filters gives wrong results.
- ALLSELECTED is great for visuals but can be confusing if slicers are not set properly.
- VALUES is preferred for counts, DISTINCT is better for lists.
12. Conclusion
Filter functions are the backbone of advanced DAX calculations. They allow you to decide exactly which rows should be considered in your calculation.
Here’s a quick summary:
- Use FILTER() when you want conditional filtering.
- Use ALL() / REMOVEFILTERS() to ignore filters.
- Use ALLEXCEPT() to keep one filter but ignore others.
- Use ALLSELECTED() when slicers and visuals are involved.
- Use VALUES() / DISTINCT() to get unique values.
- Use RELATEDTABLE() to fetch related rows.
Once you master these, you’ll be able to create dynamic, powerful measures in Power BI that go far beyond simple SUM and AVERAGE.
What’s Next?
In the next post, we’ll learn about the Time Intelligence Functions in DAX