The Most Important DAX Concept
Data Analysis Expressions (DAX)is the native query and formula language for Microsoft Power Pivot data models and SQL Server Analysis Services Tabular models. DAX includes some of the familiar Excel functions that you have frequently utilized in Excel formulas, but also includes additional functions designed to manipulate relational data as needed. Much is still to be discovered about DAX optimization. What is known is that it is powerful! DAX expressions are utilized in writing calculated columns, calculated fields (measures), and in DAX queries performed against a Power Pivot or Tabular data model. Before moving to DAX calculations and before you can understand how those DAX calculations actually work, you have to understand the most important DAX concept. What is it? Keeping the calculation simple so others can understand? No. Optimization is key though, right? Right! But not the most important. The most important thing to understand is context. Context enables dynamic analysis against a data model in which the formula results in a set or subset of rows or cells or even a singular cell. This allows a formula to be manipulated based on attribute elements or filters applied against the formula (either from within the formula itself or from the third party tool – i.e. Pivot Tables, slicers, Power View filters or cross-filter selection, etc.). Understanding context usage and utilizing it effectively is the most important DAX concept to grasp. Without understanding context, your solution may not be optimized to perform the best, may provide incorrect results, and may be too restrictive in its use.
Row Context vs Filter Context (vs Query Context?)
Row context is specific to the current row in a source table. Row context takes a column as an argument which is interpreted as a single value. Query context (per Microsoft but called Filter Context by most others) is the same concept of a cell in a Pivot Table. Query context is the end result of a filtered calculation. Query context is the cell value after filters, slicers, column and row values are applied to the calculated field. Filter context (per Microsoft) is when you “specify filter constraints on the set of values allowed in a column or table, by using arguments to a formula. Filter context applies on top of other contexts, such as row context or query context.” Filter context is applied by utilizing a DAX functions such as FILTER, ALLEXCEPT or the ALL function. Essentially, regardless of the Query context due to filters, slicers, column or row values, the Filter context is still applied (or overrides the filter context).
Calculated Column vs Calculated FieldA calculated column is calculated after data is loaded. It can be placed in columns, rows, filters, and slicers. A calculated column is evaluated in row context.
i.e. [Full Name] = [Last Name] & “, “ & [First Name]
i.e. [Sales] = SUM ( Sales[Total] )DAX sensei), who truly thinks completely outside of the box at times, started utilizing multiple tables as “levers”. It was like watching the old Lionsgate gears introduction….the doors opening to a beautiful world of DAX.
(Visited 4,724 times)