Before you create your own formulas, let’s take a look at DAX formula syntax. Syntax includes the various elements that make up a formula, or more simply, how the formula is written. For example, here’s a simple DAX formula for a measure:
This formula includes the following syntax elements:
A. The measure name, Total Sales.
B. The equals sign operator (=), which indicates the beginning of the formula. When calculated, it will return a result.
C. The DAX function SUM, which adds up all of the numbers in the Sales[SalesAmount] column. You’ll learn more about functions later.
D. Parenthesis (), which surround an expression that contains one or more arguments. Most functions require at least one argument. An argument passes a value to a function.
E. The referenced table, Sales.
F. The referenced column, [SalesAmount], in the Sales table. With this argument, the SUM function knows on which column to aggregate a SUM.
When trying to understand a DAX formula, it’s often helpful to break down each of the elements into a language you think and speak every day. For example, you can read this formula as:
For the measure named Total Sales, calculate (=) the SUM of values in the [SalesAmount ] column in the Sales table.
Context
Context is one of the most important DAX concepts to understand. There are two types of context in DAX: row context and filter context. We’ll first look at row context.
Row context
Row context is most easily thought of as the current row. It applies whenever a formula has a function that applies filters to identify a single row in a table. The function will inherently apply a row context for each row of the table over which it is filtering. This type of row context most often applies to measures.
Filter context
Filter context is a little more difficult to understand than row context. You can most easily think of filter context as: One or more filters applied in a calculation that determines a result or value.
Filter context doesn’t exist in place of row context; rather, it applies in addition to row context. For example, to further narrow down the values to include in a calculation, you can apply a filter context, which not only specifies the row context, but also specifies a particular value (filter) in that row context.
Filter context is easily seen in your reports. For example, when you add TotalCost to a visualization, and then add Year and Region, you are defining a filter context that selects a subset of data based on a given year and region.
Why is filter context so important to DAX? Because while filter context can most easily be applied by adding fields to a visualization, filter context can also be applied in a DAX formula by defining a filter using functions such as ALL, RELATED, FILTER, CALCULATE, by relationships, and by other measures and columns. For example, let’s look at the following formula in a measure named Store Sales:
To better understand this formula, we can break it down, much like with other formulas.
This formula includes the following syntax elements:
A. The measure name, Store Sales.
B. The equals sign operator (=), which indicates the beginning of the formula.
C. The CALCULATE function, which evaluates an expression, as an argument, in a context that is modified by the specified filters.
D. Parenthesis (), which surround an expression containing one or more arguments.
E. A measure [Total Sales] in the same table as an expression. The Total Sales measure has the formula: =SUM(Sales[SalesAmount]).
F. A comma (,), which separates the first expression argument from the filter argument.
G. The fully qualified referenced column, Channel[ChannelName]. This is our row context. Each row in this column specifies a channel, such as Store or Online.
H. The particular value, Store, as a filter. This is our filter context.
This formula ensures only sales values defined by the Total Sales measure are calculated only for rows in the Channel[ChannelName] column, with the value Store used as a filter.
One Comment
Mark
Thanks for your blog, nice to read. Do not stop.