Imagine your classroom has a giant sorting tray on the teacher's desk. Every crayon, sticker, and toy is laid out on that tray. This tray holds all the data. You cannot move anything off the tray or add new pieces. You can only ask the teacher to show you parts of it. When you want to see something, you walk up and say EVALUATE. That is your magic word. You must say it every single time. After you say it, you describe what you want, and the teacher puts the matching items onto a piece of paper for you. Sometimes you want things grouped by color and counted. You say EVALUATE SUMMARIZECOLUMNS and tell the teacher which groups and which counting rule to use. The teacher checks every item on the tray, pulls out the ones that match, groups them, counts them, and writes the totals on your paper. Before you say EVALUATE, you can also say DEFINE. That is like whispering a secret recipe to the teacher. Maybe you want a rule called Big Sellers that counts only toys sold more than ten times. DEFINE lets you write that recipe. The recipe only lasts while the teacher works on your question. It does not stay in the teacher's book forever. You can also ask the teacher to sort the paper using ORDER BY. The failure happens when you forget EVALUATE. You might write a beautiful recipe in DEFINE, but if you skip EVALUATE, the teacher hands you nothing. The paper stays blank. EVALUATE is always required.
Both are the one non-negotiable trigger: just as the teacher hands back nothing until you say the magic word, DAX returns no result table until EVALUATE appears in the query.
Both define a temporary rule that shapes the answer but disappears when the session ends, because DEFINE MEASURE creates a query-scoped measure that overrides the model measure only for the current query and does not permanently change the model.
Both specify which categories to group by and which aggregation to apply across all matching rows, because SUMMARIZECOLUMNS takes group-by columns and measure expressions and returns one summarized row per unique combination.
Both control how the result rows are sorted after the data is gathered, because ORDER BY sorts the final result table without affecting which rows are included or how measures are calculated.
Both narrow which data the calculation sees before it runs, because filter context in DAX limits the rows a measure evaluates, just as drawing the curtain hides items outside the boundary so the teacher only counts what is visible.
Return clothing orders and average profit per order grouped by month, sorted by month number
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Month Name],
'Date'[Month of Year],
'Product'[Category],
FILTER(
VALUES('Product'[Category]),
[Category] = "Clothing"
),
"Orders", [Orders],
"Avg Profit per Order", DIVIDE(
[Total Sales Profit],
[Orders]
)
)
ORDER BY 'Date'[Month of Year] ASCSUMMARIZECOLUMNS
You need to group rows by one or more columns and apply measures or aggregations to each group, which is the standard way to build a summarized result table in a DAX query.
Referencing a raw table by name returns every row and column with no grouping or aggregation, so you get a flat dump rather than summarized totals per category and month.
FILTER inside SUMMARIZECOLUMNS
You want to restrict the groups returned to only rows where a column meets a condition, such as showing only the Clothing category instead of all categories.
Without FILTER, SUMMARIZECOLUMNS returns all categories including ones you do not need, making the result table larger and harder to read.
ORDER BY 'Date'[Month of Year] ASC
You need the result rows sorted by a numeric month column so months appear in calendar order from January to December.
The docs state that sort-by-column properties defined in semantic models do not apply to DAX query results, so you must explicitly include the sort column in the query and in ORDER BY.
SUMMARIZECOLUMNS is the teacher grouping all items on the tray by color and month and writing the totals on the paper, FILTER is the curtain hiding non-Clothing items before counting starts, and ORDER BY is asking the teacher to arrange the rows from month one to month twelve before handing the paper to you.
Define a temporary measure to test a new calculation without permanently changing the semantic model
DEFINE
MEASURE 'Pick a sales measure'[Orders per Customer] = DIVIDE(
[Orders],
[Customers],
0
)
EVALUATE
SUMMARIZECOLUMNS(
'Date'[Fiscal Year],
"Orders", [Orders],
"Customers", [Customers],
"Orders per Customer", [Orders per Customer]
)DEFINE MEASURE
You want to try out a new measure formula or override an existing model measure just for this query, without committing the change to the model, so you can verify the result before using CodeLens to push it to the model.
Editing the model measure immediately changes it for all reports and users, whereas DEFINE MEASURE keeps the change isolated to the current query so you can confirm it returns the right numbers first.
DEFINE MEASURE is whispering a secret recipe to the teacher before asking your question; the recipe shapes what gets counted on your paper, but the teacher's official recipe book stays unchanged until you choose to write it in.
In DAX queries, EVALUATE is always required and is the trigger that produces a result table, while DEFINE lets you create temporary measures that test ideas without touching the real semantic model.