chapter four

4 Context shaping-core functions

 

This chapter covers

  • Identifying filter-shaping core functions
  • 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

Most DAX measures do not start with a number—they start with a table.

Even when a measure returns a single scalar, the engine first determines which rows are visible under the current filter context, then aggregates over that rowset. In Power BI, that rowset is shaped continuously by slicers, visual interactions, and relationships in the semantic model. Context-shaping functions are the tools that let you take control of that process.

The focus here is on a practical set of table and filter-context functions that do not alter the semantic model itself but do change the table expressions evaluated within it. They make it possible to construct virtual recordsets that behave much like subqueries, make visible scope explicit, and write measures that remain stable as users slice, drill, and cross-filter a report.

The progression starts with the simplest idea: identifying which values are currently visible. From there, we move into creating custom tables, grouping rows, and applying filters deliberately so that DAX logic stays readable, flexible, and interaction safe.

4.1 The rowset-first principle

4.2 VALUES(): returning distinct values with context awareness

4.3 The three pillars of table construction in DAX

4.3.1 Grouped aggregation with context awareness: SUMMARIZECOLUMNS

4.3.2 Column projection without aggregation: SELECTCOLUMNS

4.3.3 Row-level extension with calculated fields: ADDCOLUMNS

4.4 CALCULATETABLE: Redefining filter context

4.5 FILTER vs. CALCULATETABLE: row filtering vs. context rewrite

4.6 Combining table functions for real-world DAX logic

4.6.1 ADDCOLUMNS + VALUES (table-enrichment pattern)

4.6.2 SELECTCOLUMNS + VALUES

4.6.3 ADDCOLUMNS + FILTER

4.6.4 FILTER around ADDCOLUMNS

4.6.5 ADDCOLUMNS + CALCULATETABLE

4.6.6 SELECTCOLUMNS + CALCULATETABLE

4.7 Composing CTE equivalents with core functions

4.7.1 Multi-step aggregation (nested summarizations)

4.7.2 Conditional join via filtered sub table: California Reseller Sales

4.8 Summary