DAX Deep Dive 08: The Decisive Difference Between ALL vs. VALUES Filter Design

When using the FILTER function inside CALCULATE, deciding whether to set the stage with ALL or VALUES is a critical design choice. It determines whether you will "acknowledge the authority of external slicers or completely ignore them."

By comparing the two codes side-by-side, let us analyze how the results of independent evaluations within the engine differ.

1. Example Data for Analysis: SalesData (Fact Table)

This data contains 20 transactions that occurred during a single day.

Date	Time	ID	ProductName	Category	SalesAmount
2024-01-01	8:30	C01	Americano	Beverage	45
2024-01-01	8:45	C02	Cafe Latte	Beverage	8
2024-01-01	9:15	C01	Americano	Beverage	15
2024-01-01	10:00	C03	Whole Bean	Goods	150
2024-01-01	10:30	C04	Cookie	Bakery	4
2024-01-01	11:20	C05	Caffe Mocha	Beverage	25
2024-01-01	12:10	C07	Orange Juice	Beverage	30
2024-01-01	12:45	C08	Salted Bread	Bakery	22
2024-01-01	13:30	C05	Caffe Mocha	Beverage	12
2024-01-01	14:15	C02	Cafe Latte	Beverage	7
2024-01-01	14:50	C06	Earl Grey	Beverage	5
2024-01-01	15:20	C07	Orange Juice	Beverage	20
2024-01-01	16:00	C09	Green Tea	Beverage	6
2024-01-01	16:40	C10	Cold Brew	Beverage	18
2024-01-01	17:10	C11	Lemonade	Beverage	9
2024-01-01	18:00	C03	Whole Bean	Goods	50
2024-01-01	18:30	C12	Iced Tea	Beverage	7
2024-01-01	19:15	C13	Smoothie	Beverage	28
2024-01-01	20:00	C14	Espresso	Beverage	4
2024-01-01	21:00	C15	Chamomile	Beverage	6

Total Sales = SUM(SalesData[SalesAmount])

2. Comparison of Measure Codes

The two codes placed side-by-side differ only in the first argument (table source) of the FILTER function. This small difference creates a massive divergence in results during the engine's 'filter reassembly' process.

Version A : Absolutes Standard (ALL)

Version B : Relative Validation (VALUES)

3. The Scope of the 'Stage (Table)' Prepared by the Manager

Borrowing a previous analogy, the FILTER function only operates within the 'ingredient basket' prepared by the kitchen manager.

• The ALL Version Basket: The manager goes to the warehouse and brings back the entire product list of our store. It does not matter whether the user selected 'Americano' or 'Espresso' in the slicer.
• The VALUES Version Basket: The manager brings only the list of products currently ordered by the customer (selected in the slicer). If the customer selected only 'Espresso', the basket contains exactly one item: Espresso.

4. The 'ALL vs. VALUES' Showdown with REMOVEFILTERS

1) Filter Control Area (Left Panel)

This is where the external environment (Filter Context) is injected into the engine.

• Product Name Slicer: SalesData[ProductName]
• Category Slicer: SalesData[Category]

2) Logic Tracking Table (Right Area)

This is the core of the dashboard. It dissects what is happening inside the engine row by row.

• By adding a simple measure called Is_Over_10 (IF([Total Sales] > 10, "PASS", "FAIL")), we can logically explain why results disappear in the VALUES version.

3) Analysis of the Decisive Difference Between the Two Measures

① FILTER(ALL('SalesData'[ProductName]), ...)
"Completely ignore the product name filter, search the entire warehouse, and find high-performing beverages."

• Operation: Even before REMOVEFILTERS clears the product name filter, ALL has already set the entire product list as the stage.
• Scope: It does not matter what is manipulated in the product name slicer. It only looks at the Category = "Beverage" condition and the Total Sales > 10 condition.
• Result: It outputs 208, which is the sum of all beverages with sales exceeding 10 in the current data (Americano, Caffe Mocha, Orange Juice, Cold Brew, Smoothie).
• Business Meaning: "Among all the beverages we sell, what is the total sales of the high-performers?"

② FILTER(VALUES('SalesData'[ProductName]), ...)
"Pick out the high-performers only from the products currently displayed on the screen."

• Operation: VALUES snatches only the product names that have survived the current filter context. Since the category is currently selected as "Beverage", VALUES places only the product list belonging to the beverage category into the basket.
• The Conflict:
  • REMOVEFILTERS clears the product name filter to make the canvas clean.
  • However, VALUES draws back the beverage list it just brought in onto the canvas.
• Result: If viewing row by row in a report table, figures appear only when each product's sales exceed 10. In a Total row, the result may seem identical to Version A, but a difference occurs the moment a specific product is clicked in the slicer.
• Business Meaning: "Within this range I have currently filtered, what is the performance of the high-performing items?"
• Note: Consequently, on the canvas cleared by REMOVEFILTERS, it ends up redrawing only the products selected by VALUES.

5. Practical Data Simulation (When 'C14 Espresso, Sales 4' is Selected)

Assume we select 'Espresso' in the slicer, which has sales of less than 10 among our 20 rows of data.

Result A: ALL Version → Outputs 208

• Operation: ALL ignores the slicer (Espresso) and pulls all products from the warehouse. It finds and sums all 'PASS' items from the table above.
• Result: While the slicer shows Espresso, the measure card outputs 208.
• Intent: It shows a benchmark: "Regardless of the current selection, this is the total sales of our store's high-performing beverages."

Result B: VALUES Version → Outputs Blank

• Operation: VALUES puts exactly one item—Espresso—selected from the slicer into the basket.
• Independent Evaluation: It checks if the total sales of Espresso (4) is greater than 10. → FAIL
• Result: Since the basket becomes empty, the final total becomes Blank.
• Intent: This is validation logic: "If the selected product does not meet the high-performer criteria, I will not display the figure."

6. Key Summary Table

Category	FILTER(ALL(...))	FILTER(VALUES(...))
Key Keyword	Ignore and Expand	Respect and Validate
Slicer Influence	Does not react to filters (Fixed value)	Sensitive to filter changes
Calculation Logic	Sums all items that pass the criteria	Sums only if the selected item passes the criteria
Business Meaning	"What are the total high-performer sales for the whole store?"	"Is what I've picked right now a high-performer?"

7. Wrapping up

• If you want to always show an overall statistical value (benchmark) regardless of other filter conditions in the report, use ALL.
• If you want the logic to operate only within the range selected by the user while preventing other incidental filter interference, use VALUES.

Once you understand this difference, you are no longer just a user who copies and pastes formulas; you are a DAX designer who can freely expand and narrow the stage of data.

<Other posts on the blog>

• • The Complete Guide to the ADDCOLUMNS Function: Extending Tables with DAX
• • DAX Deep Dive 01 : Why Individual Rows Appear While the Total Vanishes When Using IF and RANKX
• • Mastering BLANK in Power BI [Part 3-4]: No Errors, but the KPI is Wrong – Silent Errors & Filter Conflicts
• • [Power BI] DAX ALL Function Practical Series ② Ranking Part 4: Cross Ranking Analysis (Store × Product)
• • DAX Deep Dive 06 : Power BI DAX Master Guide – Everything About SELECTEDMEASURE()