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.
Tags:
Power BILeave a Comment Cancel Reply
Recent Comments
Archives
Categories
Search
By transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyBy transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyBy transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyCategories
- Power BI (7)
One Comment
Mark
Thanks for your blog, nice to read. Do not stop.