chapter three

3 Model manipulation core functions

 

This chapter covers

  • Essential set of core functions designated to manipulate the model
  • Using VALUES to extract distinct values
  • Creating custom tables with SELECTCOLUMNS and ADDCOLUMNS
  • Grouping data using SUMMARIZECOLUMNS
  • Filtering rows with FILTER and CALCULATETABLE
  • Combining core functions to build flexible, reusable logic

We know that in DAX, queries are essentially the result of functions that return tables—commonly referred to as table functions. Since DAX as a language is heavily dependent on table expressions interacting dynamically over the model, it is essential to understand how to construct correct table expressions.

In DAX, table functions form the foundation of most calculations. Nearly all meaningful computations begin with a table expression that defines which data is visible and how it is filtered in the model. These functions are not just tools for retrieving data—they actively alter and shape the context in which DAX operates, serving as the core instruments for interacting with your data model.

3.1 Introduction to model manipulation core functions

This chapter explores a fundamental set of DAX functions designated as model manipulation functions. These functions enable you to navigate the semantic model, construct custom data structures, and exert fine-grained control over filter behavior and measure results. They are essential for writing modular, efficient, and maintainable DAX code.

Mastering these functions empowers you to:

3.2 VALUES — returning distinct values with context awareness

3.3 The three pillars of table construction in DAX

3.3.1 Grouped aggregation with context awareness: SUMMARIZECOLUMNS

3.3.2 Column projection without aggregation: SELECTCOLUMNS

3.3.3 Row-Level extension with calculated fields: ADDCOLUMNS

3.4 Redefining filter context: CALCULATETABLE

3.5 Combining table functions for real-world DAX logic

3.5.1 ADDCOLUMNS + VALUES

3.5.2 SELECTCOLUMNS + VALUES

3.5.3 ADDCOLUMNS + FILTER

3.5.4 FILTER around ADDCOLUMNS

3.5.5 ADDCOLUMNS + CALCULATETABLE

3.5.6 SELECTCOLUMNS + CALCULATETABLE

3.6 Composing CTE equivalents with core functions

3.6.1 Multi-Step aggregation (nested summarizations)

3.6.2 Conditional join via filtered sub table: California Reseller Sales

3.6.3 Derived columns in subqueries

3.6.4 Filtered subset with a time slice

3.6.5 Conditional subgrouping with aggregation

3.7 Summary