Imagine you're building a sales dashboard in Power BI. Your report shows total revenue by region, filtered down to the current year by a slicer. Everything looks great — until your manager asks, "Can you show each region's sales as a percentage of the total company revenue, not just the filtered amount?" You stare at your measure. The number in the denominator keeps changing whenever someone clicks the slicer. You can't figure out how to make it hold still.
This is the moment you discover that DAX doesn't just calculate values — it calculates values within a context. And that context is controlled, bent, and sometimes completely overridden by a family of functions called table functions. Understanding how FILTER, ALL, ALLEXCEPT, and VALUES work isn't just a nice-to-have skill. It's the difference between measures that do what you intend and measures that silently lie to you.
By the end of this lesson, you'll know exactly how each of these functions manipulates the filter context in Power BI, when to reach for each one, and how to combine them to answer real analytical questions. We'll build every example around a realistic retail sales scenario so the concepts stick.
What you'll learn:
FILTER creates a new filtered table based on row-by-row conditionsALL removes filters to let you calculate against a full datasetALLEXCEPT removes some filters while keeping othersVALUES returns the distinct values in a column, and why it's safer than you thinkYou should be comfortable with the following before continuing:
SUM, AVERAGE, or COUNTNo prior experience with table functions is required.
Before we touch a single function, you need to understand the environment these functions operate in.
Every DAX measure you write gets evaluated inside a filter context — a set of rules that says "right now, only these rows are visible." When you drop a measure into a table visual that has a Region column, Power BI automatically creates a separate filter context for each row: one where Region = "North," one where Region = "South," and so on. Your measure recalculates separately inside each of those invisible boxes.
Think of filter context like a spotlight on a stage. The full dataset is the entire stage with all the performers. The spotlight narrows down to illuminate only the performers relevant to the current cell in your visual. SUM(Sales[Revenue]) only adds up the revenue of performers standing in the spotlight.
The table functions in this lesson are tools for moving the spotlight. FILTER makes it smaller and more precise. ALL yanks it wide open. ALLEXCEPT opens it wide except for one or two things you want to keep in focus. VALUES looks at who's currently standing in the light and tells you their names.
Throughout this lesson, we'll work with a simple retail dataset containing three tables:
Sales table with columns: OrderID, CustomerID, ProductID, SaleDate, Revenue, Quantity
Products table with columns: ProductID, ProductName, Category, UnitCost
Regions table with columns: CustomerID, Region, Country
Our base measure, which we'll reference throughout, is:
Total Revenue = SUM(Sales[Revenue])
Keep this measure in mind — it's the foundation everything else builds on.
FILTER is a table function — meaning it doesn't return a number, it returns a table. Specifically, it returns a version of a table where only the rows meeting your condition survive. You almost never use FILTER on its own in a measure; instead, you hand its result to another function like CALCULATE or SUMX that knows what to do with a table.
The syntax is:
FILTER( <table>, <condition> )
Where <table> is the table to filter and <condition> is a Boolean (true/false) expression evaluated row by row.
The built-in filter arguments inside CALCULATE are powerful but they only handle simple equality conditions like Sales[Year] = 2023. The moment you need something more nuanced — "give me rows where Revenue is greater than 500" or "give me rows where the margin percentage exceeds 20%" — you need FILTER.
Suppose your manager wants a measure showing revenue from orders over $1,000 — regardless of any other filters on the report.
High Value Revenue =
CALCULATE(
SUM(Sales[Revenue]),
FILTER(
Sales,
Sales[Revenue] > 1000
)
)
Here's what's happening step by step:
FILTER(Sales, Sales[Revenue] > 1000) scans every row in the Sales table and returns a new in-memory table containing only the rows where Revenue exceeds 1,000.CALCULATE receives that filtered table and evaluates SUM(Sales[Revenue]) within it.Now let's say you want revenue from the Electronics category only, calculated as a measure so you can use it in multiple visuals:
Electronics Revenue =
CALCULATE(
SUM(Sales[Revenue]),
FILTER(
Products,
Products[Category] = "Electronics"
)
)
Because Sales and Products have a relationship defined in your model, CALCULATE respects that relationship when you pass it a filtered Products table. Power BI follows the relationship to find the matching sales rows.
Tip: When your condition involves a column from a related table, always use
FILTERon that related table. Trying to filter directly on a column that doesn't exist in theSalestable will cause an error.
A common beginner mistake is wrapping everything in FILTER out of habit. For simple column equality conditions, you can pass the condition directly to CALCULATE without FILTER:
-- Less efficient (FILTER version):
Electronics Revenue =
CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Category] = "Electronics"))
-- More efficient (direct filter argument):
Electronics Revenue =
CALCULATE(SUM(Sales[Revenue]), Products[Category] = "Electronics")
DAX can optimize simple column filter arguments much better than it can optimize a row-by-row FILTER scan. Save FILTER for conditions that genuinely require row-by-row evaluation, like comparisons, percentages, or expressions involving multiple columns.
ALL removes filters. When used inside CALCULATE, it tells DAX: "Ignore whatever filters have been applied to this column or table, and calculate as if everything is visible."
The syntax has two common forms:
ALL( <table> ) -- Remove all filters from the entire table
ALL( <table>[<column>] ) -- Remove filters from a specific column only
Let's revisit the problem from the introduction. You want to show each region's revenue as a percentage of total company revenue. Here's the measure:
Revenue % of Total =
DIVIDE(
SUM(Sales[Revenue]),
CALCULATE(SUM(Sales[Revenue]), ALL(Sales))
)
Walk through the logic:
SUM(Sales[Revenue]), runs inside the current filter context. If you're in the "North" row, it sums only North's revenue.CALCULATE(SUM(Sales[Revenue]), ALL(Sales)), strips away all filters on the Sales table. It doesn't care whether you're looking at North or South — it always returns the grand total.DIVIDE safely handles the division (avoiding divide-by-zero errors automatically).Now when a user applies a Year slicer, the numerator correctly narrows to the selected year, but wait — so does the denominator. If you want the denominator to always reflect the all-time total no matter what slicers are active, you'd write:
Revenue % of All Time Total =
DIVIDE(
SUM(Sales[Revenue]),
CALCULATE(SUM(Sales[Revenue]), ALL(Sales))
)
And here's something subtle: ALL(Sales) removes ALL filters on the Sales table, including ones coming from the Year slicer. The denominator is always the grand total across all years. Whether that's what you want depends on the business question — and now you have the tool to make either choice deliberately.
Sometimes you only want to remove the filter on one column, not the whole table. Say you have a report filtered by both Year and Region, and you want to compare each region's revenue to the total for that same year (not the all-time total). You'd remove the Region filter but keep the Year filter:
Revenue vs Year Total =
DIVIDE(
SUM(Sales[Revenue]),
CALCULATE(SUM(Sales[Revenue]), ALL(Regions[Region]))
)
By specifying ALL(Regions[Region]) instead of ALL(Sales), you remove only the Region filter. The Year filter from the slicer remains active in the denominator, so you're dividing by "total revenue for this year across all regions."
Warning:
ALLused outside ofCALCULATEbehaves differently — it returns the full table as a result, ignoring filters. InsideCALCULATE, it acts as a filter modifier. Don't confuse the two contexts. In practice, you'll almost always useALLinsideCALCULATE.
ALLEXCEPT is the complement of ALL. Instead of specifying what to remove, you specify what to keep, and everything else gets removed.
ALLEXCEPT( <table>, <column1>, <column2>, ... )
This says: "Remove all filters on <table> except for filters on these specific columns."
Imagine you're building a leaderboard. You want to rank salespeople by revenue, but only within their own region. When someone filters to the "West" region, ranks should reset from 1 within the visible salespeople.
Without ALLEXCEPT, you'd have to manually remove every column filter except Region — and if your model adds new columns later, your measure would silently break.
With ALLEXCEPT, you write:
Region Revenue Total =
CALCULATE(
SUM(Sales[Revenue]),
ALLEXCEPT(Regions, Regions[Region])
)
This removes all filters on the Regions table except Region. So if a visual filters down to salesperson "Maria" in the "West" region, this measure still returns the total revenue for all salespeople in "West" — because the filter on the individual salesperson has been removed, but the filter on Region is preserved.
You'd then use this as the denominator in a ranking or percentage calculation.
You might wonder: "Can't I just do ALL(Regions[Salesperson]) to remove only the salesperson filter?" Yes, in simple cases. But ALLEXCEPT is more maintainable. If your table has ten filter-able columns and you want to remove all of them except one, ALLEXCEPT requires one line instead of ten.
Tip: Use
ALLEXCEPTwhen you have a table with many columns that might carry filters and you want to preserve filters on just one or two of them. Use individualALL(Table[Column])calls when you have a specific, limited set of columns to clear.
VALUES is different from the other three functions. Rather than manipulating filters, it reads them. VALUES returns a single-column table containing the distinct values from a column as filtered by the current context.
VALUES( <column> )
If the current filter context has narrowed the Region column to just "West" and "South," then VALUES(Regions[Region]) returns a two-row table: "West" and "South."
A common pattern is checking whether a slicer has exactly one item selected. This is useful for conditional formatting, dynamic titles, or measures that only make sense for a single selection:
Selected Region =
IF(
HASONEVALUE(Regions[Region]),
VALUES(Regions[Region]),
"Multiple Regions"
)
HASONEVALUE is a helper function that returns TRUE if the column has exactly one visible value. If it does, VALUES returns that single value (which DAX can treat as a scalar in this context). If not, we return a fallback string.
VALUES becomes especially powerful in iterating functions like SUMX or AVERAGEX. Suppose you want the average revenue per region, but you want to calculate it at the region level first and then average those regional totals — not just average all individual rows:
Avg Revenue per Region =
AVERAGEX(
VALUES(Regions[Region]),
CALCULATE(SUM(Sales[Revenue]))
)
Here's what happens:
VALUES(Regions[Region]) returns a table of distinct regions visible in the current context.AVERAGEX iterates over that table, and for each region, evaluates CALCULATE(SUM(Sales[Revenue])) — which picks up the region as a filter automatically.This is a fundamentally different number than AVERAGE(Sales[Revenue]). Understanding which average your business question requires is half the battle.
Tip:
VALUESincludes a blank row if there are unmatched rows in your relationships. If you want strictly the values from the data without any blank, useDISTINCTinstead. For most measures,VALUESis correct — it participates properly in context transitions. UseDISTINCTwhen you're sure blanks from relationship breaks would pollute your result.
The real power emerges when you combine these functions. Here's a complete example: a measure that calculates what percentage of category revenue each product represents, regardless of any product-level filters on the visual.
Product % of Category =
VAR CurrentProductRevenue = SUM(Sales[Revenue])
VAR CategoryRevenue =
CALCULATE(
SUM(Sales[Revenue]),
ALLEXCEPT(Products, Products[Category])
)
RETURN
DIVIDE(CurrentProductRevenue, CategoryRevenue)
This measure:
ALLEXCEPT to compute the total for the whole category (removing product-level filters but keeping the category filter)If a user also filters by Year using a slicer, both the numerator and denominator respect that Year filter — because ALLEXCEPT only removes filters from the Products table, not from the Sales table or date-related tables.
Let's put everything together. Using the scenario tables described earlier (Sales, Products, Regions), create the following four measures. Take time to type each one out manually rather than copying — this builds muscle memory.
Exercise 1: Create a measure called Revenue Above Average that returns the sum of revenue only for orders where the individual order's revenue is above $500. Use FILTER on the Sales table.
Exercise 2: Create a measure called % of Grand Total that shows the current filtered revenue as a percentage of all revenue across the entire Sales table, regardless of slicers.
Exercise 3: Create a measure called % of Country Total that shows each region's revenue as a percentage of the total for its country. Use ALLEXCEPT to preserve the Country filter while removing the Region filter.
Exercise 4: Create a measure called Dynamic Title that uses VALUES and HASONEVALUE to return the selected category name if one category is selected, or "All Categories" if multiple are selected. Place this measure in a card visual.
Once you've written each measure, drop them into a table visual that includes Region and Category columns, and add a slicer for Year. Observe how each measure responds differently as you change the slicer selection.
Mistake 1: Using FILTER when a direct column argument would work FILTER iterates row by row and can be slow on large tables. If your condition is a simple equality check on a column, pass it directly to CALCULATE as a filter argument. Reserve FILTER for complex conditions.
Mistake 2: Confusing ALL inside vs. outside CALCULATE
ALL(Sales) by itself returns the entire Sales table with no filters applied — useful in SUMX or as a base table. Inside CALCULATE, it's a filter modifier that clears context. The same function word behaves differently depending on where it sits.
Mistake 3: Expecting ALLEXCEPT to work on columns from other tables
ALLEXCEPT(Products, Regions[Region]) will cause an error or unexpected behavior. The columns you specify to keep must belong to the same table as the first argument.
Mistake 4: VALUES returning a blank row
If your relationships have unmatched rows (a Sales row whose ProductID doesn't exist in the Products table), VALUES(Products[Category]) will include a blank. This can inflate counts or cause unexpected behavior. Check your relationships first. If blanks are genuinely a problem, switch to DISTINCT.
Mistake 5: Thinking FILTER "permanently" changes the table
FILTER returns an in-memory virtual table. It doesn't modify your source data or the underlying table in your model. Everything FILTER produces exists only for the duration of that single measure evaluation.
You now have four powerful tools in your DAX toolkit:
These four functions aren't isolated tricks — they're the vocabulary you'll use every day to express complex analytical logic clearly and correctly. Together, they give you full control over what data is visible when a measure evaluates.
Where to go next:
Learning Path: DAX Mastery