We released a new session in SQLBI+:
How to navigate the lattice of visual calculations: This video explains how to navigate the lattice of visual calculations in DAX, using COLLAPSEALL and EXPAND to move between levels. A key topic is the difference between ROWS/COLUMNS and VALUES of ROWS/COLUMNS. The video also shows how to create reusable visual calculation functions that encapsulate lattice navigation logic, simplifying otherwise complex code.
Stay tuned for new SQLBI+ content coming in 2026!
Retrieves a context with removed detail levels along an axis. With an expression, returns its value in the new context, enabling navigation to the highest level on the axis and is the inverse of EXPANDALL.
COLLAPSEALL ( [<Expression>], <Axis> )
Retrieves a context with added levels of detail compared to the current context. If an expression is provided, returns its value in the new context, allowing for navigation in hierarchies and calculation at a more detailed level.
EXPAND ( [<Expression>] [, <Axis>] [, <Column> [, <Column> [, … ] ] ] [, <N>] )
When a column name is given, returns a single-column table of unique values. When a table name is given, returns a table with the same columns and all the rows of the table (including duplicates) with the additional blank row caused by an invalid relationship if present.
VALUES ( <TableNameOrColumnName> )