Introduction to DAX
Data analysis expressions (DAX) can be used with Business Central and Power BI on reports.
What are data analysis expressions (DAX)
DAX is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.
Why DAX is so important
It's easy to create a new Power BI Desktop file and import some data into it. You can even create reports that show valuable insights without using any DAX formulas at all. But what if you need to analyze growth percentage across product categories and for different date ranges? Or you need to calculate year-over-year growth compared to market trends? DAX formulas provide this capability and many other important capabilities as well. Learning how to create effective DAX formulas will help you get the most out of your data. When you get the information you need, you can begin to solve real business problems that affect your bottom line. This is the power of Power BI, and DAX will help you get there.
The best way to learn DAX is to create some basic formulas, use them with actual data, and see the results for yourself.
DAX syntax
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 how the formula is written. For example, here's a simple DAX formula for a measure:
This formula includes the following syntax elements:
The measure name, Total Sales.
The equals sign operator (=), which indicates the beginning of the formula. When calculated, it will return a result.
The DAX function SUM, which adds up all of the numbers in the OrderIntake[AmountOutstanding] column.
Parentheses (), which surround an expression that contains one or more arguments. All functions require at least one argument. An argument passes a value to a function.
The referenced table, OrderIntake.
The referenced column, [AmountOutstanding] in the OrderIntake 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 [AmountOutstanding] column in the OrderIntake table.
When added to a report, this measure calculates and returns values by summing up sales amounts for each of the other fields we include.
You might be thinking, "Isn't this measure doing the same thing as if I were to just add the AmountOutstanding field to my report?" Well, yes. But there's a good reason to create our own measure that sums up values from the AmountOutstanding field. We can use it as an argument in other formulas. This may seem a little confusing now, but as your DAX formula skills grow, knowing this measure will make your formulas and your model more efficient. In fact, you'll see the Total Sales measure showing up as an argument in other formulas later.
Let's go over a few more things about this formula. We introduced a function, SUM. Functions are pre-written formulas that make it easier to do complex calculations and manipulations with numbers, dates, time, text, and more.
You also see that the column name [AmountOutstanding] was preceded by the OrderIntake table in which the column belongs. This name is known as a fully qualified column name in that it includes the column name preceded by the table name. Columns referenced in the same table do not require the table name be included in the formula, which can make long formulas that reference many columns shorter and easier to read. However, it is a good practice to include the table name in your measure formulas, even when in the same table.
It is important your formulas have the correct syntax. In most cases, if the syntax isn't correct, a syntax error is returned. In other cases, the syntax may be correct, but the values returned might not be what you are expecting. The DAX editor in Power BI Desktop includes a suggestions feature, used to create syntactically correct formulas by helping you select the correct elements.
DAX 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 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 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 Total Sales Quotes:
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, Total Sales Quotes.
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. Parentheses (), which surround an expression containing one or more arguments.
E. A [Total Sales] measure in the same table as an expression. The Total Sales
measure has the formula: = sum(OrderIntake_PowerBI[AmountOutstanding]).
F. A comma or semicolon (, or ;), which separates the first expression argument from the filter argument.
G. The fully qualified referenced column OrderIntake[DocumentType]. This is our row context. Each row in this column specifies a document type, such as Quote or Order or Invoice.
H. The particular value, Quote, 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 OrderIntake[DocumentType] column, with the value Quote used as a filter.
As you can imagine, being able to define filter context within a formula has immense and powerful capabilities. The ability to reference only a particular value in a related table is just one such example. Don't worry if you don't completely understand context right away. As you create your own formulas, you'll better understand context and why it's so important in DAX.
Now that you have a basic understanding of the most important concepts in DAX, you can begin creating DAX formulas for measures on your own. DAX can indeed be a little tricky to learn, but there are many resources available to you. After experimenting with a few of your own formulas, you can learn more about other DAX concepts and formulas that can help you solve your own business problems. There are many DAX resources available to you; the most important is the Data Analysis Expressions (DAX) Reference.
Because DAX has been around for several years in other Microsoft BI tools such as Power Pivot and Analysis Services Tabular models, there is a lot of great information available out there. You can find more information in books, whitepapers, and blogs from both Microsoft and other leading BI professionals.