DAX, the basic concepts

DAX (Data Analysis Expressions) is a language used to create expressions (formulas) for making the reporting dimensions that are used in Power Pivot tables. Once created these expressions can be both used within tabular model of Microsoft SQL Server Analysis Services. A large number of DAX functions have the same syntax as Excel functions, while others can work with relational data and perform dynamic aggregation of data, their filtering etc.

Introduction may seem daunting, but the DAX expressions do not differ much from the formula that you created earlier in Excel. They are used to form:

• Calculated Columns
• Measures

Calculated columns greatly resemble the columns that are added in structured tables. They are important because, in addition to creating a reporting dimension, could be used for creating Slicers and Timeline interactive filters.

Measures are formulas that are written directly in the model, and we can use them to create a much more complex reporting dimensions. In Excel, version 2013, measures are called Calculated Fields, a version of 2016 again they got their original name (Measures).

In addition to the measures that we have created (explicit measures), there are implicit measures that are automatically generated by dragging reporting dimensions in the field of aggregation of data (Values). For example, dragging the dimension KOL (quantity) in the field of aggregate data (Values) implicitly we create measure SUM of KOL. Since version 2016 implicit measures could be shown in the model. This is achieved by choosing the Show Implicit Measures option in the Advanced ribbon.

Let’s create two simple DAX expressions! By clicking Manage the Power Pivot ribbon we open window of the Data model, and then should position ourselves to the table (we have previously added to the Model) in which we want to create calculated columns and measures.

To add a calculated column, you can click below the Add Column label, to the far right of the table. Then, in the formula box we write the expression so that it starts with a “=”, and continues with a formula. As we start to write formula an equations driven mechanism known as Intellisense helps us by suggesting syntax of functions, which greatly simplifies the process of their writing. In our case, we chose a table Artikli, we want to create an expression that calculate price divided by EUR exchange rate:

=[PC]/120

Once we enter the new formula column will automatically populate it with calculated values. It receives a temporary name CalculatedColumn <n> (n is an integer number). Because this name is not appropriate, we will change it by right-clicking on the column name and then select option Rename Column. Column header will change color and we can give a more appropriate name, for example. “PC (EUR)”. In this menu, there are several options for handling calculated columns, by which we can hide, freeze, change column width etc.

Whether we are creating calculated column or measures, as soon as we do, should determine how to display their content (value formatting). Easiest way to do that is to go to Home ribbon, and then in Formatting section choose how to format values (General, Decimal, Percent, Currency etc.). Default value for each new formula is General, and new format should be applied depending of formula result. For example, earlier created calculated column “PC (EUR)” should be formatted as a decimal number with two decimal places.

We can create measures in two ways. The first way would be to click the area below the data table (Calculation Area), in one of the tables where we want to create the measure. Then we determine the name of the measures, then the symbol “:=”, and finally write the desired DAX expression. For example, if you are positioned on the table Transakcije (Transactions) you can make a measure that summarizes all sold quantities:

SumaKolicina:=SUM([KOL])

Another way to create a measure is by using option New Measure, which is located in the menu Measures in Power Pivot ribbon. After start it opens a dialog box in which the header first entry is the name of the table where we create a measure (Table Name), and then the name of the measure (Measure name). At the end, at the central part of the window is area where we input DAX expression. There is an option that can be used to test a formula, and also an option for formatting its value. Once you enter the correct formula,  measure that we have created will appear in a given table the Data model. The menu Measures contains also an option named Manage Measures, by which we can change a previously created expressions.

If we create a PowerPivot report based on the selected table, we’ll be able to see, besides other reporting dimensions, a calculated column and a measure that we just created.