How to navigate the lattice of visual calculations
This video explains how to navigate the lattice of visual calculations in DAX. The visual context goes beyond the row context and filter context by adding the concept of levels within the lattice. The video shows how to use COLLAPSEALL and EXPAND to move between levels, using COLLAPSEALL as an absolute reference point from which you can reach any desired level.
A key topic is the difference between ROWS/COLUMNS and VALUES of ROWS/COLUMNS. ROWS and COLUMNS always return all values at the current level, ignoring filters, while VALUES of ROWS/COLUMNS respects the active filter context. The video also shows how to create reusable visual calculation functions that encapsulate lattice navigation logic, simplifying otherwise complex code.
These techniques build on the article Using visual calculations to highlight an entire row: We recommend reading the article and watching its companion video before this SQLBI+ video.
Alberto Ferrari