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 Field
A 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]
A calculated field is created in the Power Pivot/Tabular measure grid or from Calculated Fields button on the Power Pivot ribbon. A calculated field can only be placed in the values area of a Pivot Table / Report. A calculated field can have Filter context applied (or not) in the definition arguments of the calculation. Query context is applied to a calculated field once it is placed into a visualization or Pivot Table. Even if there are no row or column values on the pivot table, the query context is essentially ALL.
i.e. [Sales] = SUM ( Sales[Total] )
This is somewhat of an easy concept to grasp….but not to understand in practice. Once you understand this concept, truly understand this concept, doors will open. The lights will come on. Insert whatever cliche you want here.
You will utilize tables as filters, utilize tables as cross filters, and utilize DAX filters and functions in ways unimaginable before truly understanding this concept. There will be a person, a project, or a moment where you just “get it“. For me, it was a truly profound moment (or series of moments?) on a particular project. My coworker (my 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.
Keep at it! We will be posting quite a bit more on DAX. There are several other sources out there (will provide a list of those that I frequent the most). You will get it! We hope to help you understand the Power of DAX!
Great !