chapter three

3 DAX queries and key model concepts

This chapter covers

Writing standalone DAX queries using EVALUATE
Translating SQL clauses into DAX table functions
Demonstrating the functional, declarative nature of DAX
Observing filter propagation and expanded table behavior through isolated examples

DAX becomes much easier to learn when you separate where it runs. Sometimes, you write the entire query yourself in a tool such as DAX Studio or Power BI Query view. At other times, Power BI generates the query for a visual and evaluates your measures within the filter context created by the report.

I call that second setting interactive DAX. The phrase is used as a teaching aid, and it’s not a standard industry term.

DAX queries, written in tools such as DAX Studio or Power BI Query view, give you a clean sandbox for understanding how DAX functions behave, how filters propagate, and how relationships influence results. Once those ideas are clear, the same patterns become much easier to recognize inside a live Power BI report.

The learning path begins with EVALUATE, the foundation of standalone DAX queries, and then moves into the model concepts that shape query results, including context, filter propagation, and expanded table behavior. With that foundation in place, it becomes easier to understand not only how to write DAX queries but also how Power BI evaluates DAX under report interactivity.

3.1 The Evaluate Statement

3.2 SQL versus DAX: a shift in mental models

3.2.1 Returning all rows from a table

3.2.2 Limiting rows with TOPN

3.2.3 Filtering rows by condition

3.2.4 Grouping and aggregating

3 DAX queries and key model concepts

This chapter covers

3.1 The Evaluate Statement

3.2 SQL versus DAX: a shift in mental models

3.2.1 Returning all rows from a table

3.2.2 Limiting rows with TOPN

3.2.3 Filtering rows by condition

3.2.4 Grouping and aggregating

3.2.5 Filtering aggregated results (SQL HAVING)

3.2.6 Selecting specific columns (projection)

3.2.7 Joining tables via relationships (auto-expansion)

3.3 Introduction to iterations: When table expansion is not automatic

3.3.1 Using `RELATED()` during iteration

3.3.2 Iterating over sales with SELECTCOLUMNS

3.3.3 Using `RELATEDTABLE()` for one-to-many traversal

3.3.4 Top-down aggregation from category

3.4 Building modular DAX: DEFINE, measures, and variables

3.4.1 Profit filtering with DEFINE

3.4.2 DEFINE + VAR hybrid for gross profit %

3.4.3 DEFINE scope: Query vs. Model

3.4.4 DAX lazy evaluation and performance

3.5 Summary

3 DAX queries and key model concepts

This chapter covers

3.1 The Evaluate Statement

3.2 SQL versus DAX: a shift in mental models

3.2.1 Returning all rows from a table

3.2.2 Limiting rows with TOPN

3.2.3 Filtering rows by condition

3.2.4 Grouping and aggregating

3.2.5 Filtering aggregated results (SQL HAVING)

3.2.6 Selecting specific columns (projection)

3.2.7 Joining tables via relationships (auto-expansion)

3.3 Introduction to iterations: When table expansion is not automatic

3.3.1 Using RELATED() during iteration

3.3.2 Iterating over sales with SELECTCOLUMNS

3.3.3 Using RELATEDTABLE() for one-to-many traversal

3.3.4 Top-down aggregation from category

3.4 Building modular DAX: DEFINE, measures, and variables

3.4.1 Profit filtering with DEFINE

3.4.2 DEFINE + VAR hybrid for gross profit %

3.4.3 DEFINE scope: Query vs. Model

3.4.4 DAX lazy evaluation and performance

3.5 Summary

3.3.1 Using `RELATED()` during iteration

3.3.3 Using `RELATEDTABLE()` for one-to-many traversal