chapter two

2 DAX queries and key model concepts

This chapter covers

Writing Standard DAX queries using EVALUATE
Translating SQL clauses into DAX table functions
Demonstrating the functional, non-procedural nature of DAX
Observing filter propagation and expanded table behavior through isolated examples
Applying reusable DAX patterns to build modular, maintainable analytical queries

We need to know now the fundamental building blocks of DAX syntax, expanding on semantic model concepts introduced earlier—such as physical relationships, expanded tables, and data lineage. By drawing comparisons to familiar SQL constructs, we’ll illustrate here how DAX operates in a functional, rather than procedural, manner.

This chapter serves as an entry point for readers who are new to DAX or the concept of Interactive DAX. Emphasis is placed on understanding DAX’s functional structure in the context of concepts previously discussed. In the next chapter, we delve deeper into the functional definitions and syntax of the key DAX functions introduced here.

2.1 Why write DAX queries directly?

Working with DAX outside the report canvas—free from slicers, visuals, or user-driven context—offers unmatched clarity. It allows you to:

See how DAX functions truly behave
Examine how filters propagate
Understand how relationships influence results
Debug logic without interference from interactivity

2.2 Understanding standard DAX queries

2.3 The `EVALUATE` statement

2.4 SQL versus DAX — A shift in mental models

2.5 DAX queries versus SQL query examples

2.5.1 Returning all rows from a table

2.5.2 Limiting rows with TOPN

2 DAX queries and key model concepts

This chapter covers

2.1 Why write DAX queries directly?

2.2 Understanding standard DAX queries

2.3 The `EVALUATE` statement

2.4 SQL versus DAX — A shift in mental models

2.5 DAX queries versus SQL query examples

2.5.1 Returning all rows from a table

2.5.2 Limiting rows with TOPN

2.5.3 Filtering rows by condition

2.5.4 Grouping and aggregating

2.5.5 Filtering Aggregated Results (SQL HAVING)

2.5.6 Selecting Specific Columns (Projection)

2.5.7 Joining Tables via Relationships (Auto-Expansion)

2.6 Introduction to iterations — when table expansion is not automatic

2.6.1 What are iterator functions?

2.6.2 Using `RELATED()` during iteration

2.6.3 Iterating over sales with SELECTCOLUMNS

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

2.6.5 Top-down aggregation from category

2.7 Building modular DAX — DEFINE, measures, and variables

2.7.1 Why modular DAX matters

2.7.2 Profit filtering with DEFINE

2 DAX queries and key model concepts

This chapter covers

2.1 Why write DAX queries directly?

2.2 Understanding standard DAX queries

2.3 The EVALUATE statement

2.4 SQL versus DAX — A shift in mental models

2.5 DAX queries versus SQL query examples

2.5.1 Returning all rows from a table

2.5.2 Limiting rows with TOPN

2.5.3 Filtering rows by condition

2.5.4 Grouping and aggregating

2.5.5 Filtering Aggregated Results (SQL HAVING)

2.5.6 Selecting Specific Columns (Projection)

2.5.7 Joining Tables via Relationships (Auto-Expansion)

2.6 Introduction to iterations — when table expansion is not automatic

2.6.1 What are iterator functions?

2.6.2 Using RELATED() during iteration

2.6.3 Iterating over sales with SELECTCOLUMNS

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

2.6.5 Top-down aggregation from category

2.7 Building modular DAX — DEFINE, measures, and variables

2.7.1 Why modular DAX matters

2.7.2 Profit filtering with DEFINE

2.3 The `EVALUATE` statement

2.6.2 Using `RELATED()` during iteration

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