chapter three

3 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. This chapter presents 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, the chapter illustrates how DAX operates in a functional, rather than procedural, manner.

It also 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.

3.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, and debug logic without interference from interactivity.

3.2 Understanding standard DAX queries

3.3 The EVALUATE statement

3.4 SQL versus DAX: A shift in mental models

3.5 DAX queries versus SQL query examples

3.5.1 Returning all rows from a table

3.5.2 Limiting rows with TOPN

3.5.3 Filtering rows by condition

3.5.4 Grouping and aggregating

3.5.5 Filtering aggregated results (SQL HAVING)

3.5.6 Selecting specific columns (projection)

3.5.7 Joining tables via relationships (auto-expansion)

3.6 Introduction to iterations: When table expansion is not automatic

3.6.1 What are iterator functions?

3.6.2 Using RELATED() during iteration

3.6.3 Iterating over sales with SELECTCOLUMNS

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

3.6.5 Top-down aggregation from category

3.7 Building modular DAX: DEFINE, measures, and variables

3.7.1 Why modular DAX matters

3.7.2 Profit filtering with DEFINE

3.7.3 DEFINE + VAR hybrid for gross profit %

3.7.4 DEFINE scope: Query vs. Model