Module 5: Filter context and CALCULATE · Lesson 5.7 · about 16 min
DAX queries: EVALUATE and SUMMARIZECOLUMNS
A DAX query returns a table instead of a single value. Use EVALUATE in Power BI's DAX query view to test measures, check data and answer questions fast, with SUMMARIZECOLUMNS, ADDCOLUMNS and ORDER BY.
Module 5 · about 16 minutes · read first: 5.2. You’ll be able to write DAX queries that return tables, and use them to test measures.
1. Why this matters
Every visual in Power BI runs a DAX query behind the scenes. Writing your own in DAX query view is the fastest way to test a measure, check data quality or answer a one-off question. Some interviews ask you to write one.
2. Your first queries
Run each query on its own (put the one you want last). Queries return tables, like Power BI's DAX query view.
Press Run to see the result.
EVALUATE takes any table expression: a table name, VALUES(…), FILTER(…), TOPN(…), SUMMARIZECOLUMNS(…). The editor shows only the last query in the box; comment the others out with //.
3. SUMMARIZECOLUMNS: a table visual in code
Write a query: total sales and order lines per region, sorted by sales from highest. Use SUMMARIZECOLUMNS.
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
EVALUATE
SUMMARIZECOLUMNS(
Store[Region],
"Sales", [Total Sales],
"Lines", COUNTROWS(Sales)
)
ORDER BY [Sales] DESC SUMMARIZECOLUMNS(group columns…, [filter tables…], "Name", expression, …) groups by the columns, applies any filter tables, and evaluates each expression per group. It’s exactly what a table visual does, which is why Power BI generates it.
Write a query with sales by Year and Category, filtered to the Outdoor and Electronics categories only (add a filter table argument such as TREATAS({"Outdoor", "Electronics"}, Product[Category])).
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
EVALUATE
SUMMARIZECOLUMNS('Date'[Year], Product[Category], TREATAS({"Outdoor", "Electronics"}, Product[Category]), "Sales", [Total Sales]) TREATAS({"Outdoor", "Electronics"}, Product[Category]) builds a small table of values and treats it as a filter on that column, like a slicer.
4. More query tools
| Function | Returns |
|---|---|
ADDCOLUMNS(table, "Name", expr) |
the table with new columns, worked out per row (with context transition for measures) |
SELECTCOLUMNS(table, "Name", expr) |
only the columns you list |
TOPN(n, table, orderBy) |
the top rows |
CALCULATETABLE(table, filters…) |
a table in a changed filter context |
ROW("Name", expr) |
a one-row table, handy to test a single measure |
ORDER BY expr [ASC/DESC] |
sorts the output (after EVALUATE) |
In Power BI you can also DEFINE MEASURE Sales[Test] = … at the top of a query to try a measure without adding it to the model. In this course’s editor, measures written above the query do the same.
Principle
Principle: a query is evaluated with an empty filter context (no visual, no slicers). Any filtering comes from the query itself: group columns, filter arguments or CALCULATETABLE. That makes queries a clean lab: what you see is the measure’s pure behaviour.
Try it: EVALUATE ROW("Sales", [Total Sales]) returns the grand total, because nothing filters it.
5. Worked example
Check a measure before showing it to a manager. EVALUATE SUMMARIZECOLUMNS('Date'[Year], Store[Store], "Sales", [Total Sales], "Growth", [Growth %]) shows every store and year at once. Forks Market’s 2025 growth is blank (no 2024 sales), every other store shows a sensible percentage, and the numbers match the report. Three minutes of checking saves an awkward meeting.
6. Practice
- Write a query listing products with UnitPrice over 30, sorted by price.
- Write a query for total sales per weekday, sorted from highest.
- What does
EVALUATE ROW("Lines", COUNTROWS(Sales))return?
Answers
EVALUATE FILTER(Product, Product[UnitPrice] > 30) ORDER BY Product[UnitPrice] DESC.EVALUATE SUMMARIZECOLUMNS('Date'[Weekday], "Sales", [Total Sales]) ORDER BY [Sales] DESC.- One row with one column, Lines = 7,328.
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.