The Data Analysis Expressions (DAX) language is a formula language that allows users to define custom calculations, calculated columns and measures. DAX includes some of the functions that are used in Excel formulas, and additional functions that are designed to work with relational data and perform dynamic aggregation.
DAX formulas are very similar to Excel formulas. To create a formula,type an equal sign in the formula editing dialog box or measure grid, followed by a function name or expression, and any required values or arguments.
Like Excel, DAX provides a variety of functions that you can use perform calculations using dates and times, create conditional values, and work with strings. However, DAX formulas are different in the following important ways:
-
A DAX function always references a complete column or a table. If you want to use only particular values from a table or column, you can add filters to the formula.
-
If you need to customize calculations on a row-by-row basis, DAX provides functions that let you use the current row value or a related value as a kind of parameter, to perform calculations that vary by context.
-
DAX includes many functions that return a table, rather than a value. The table is not displayed in the workbook or model, but is used to provide input to other functions. For example, you can retrieve a table and then count the distinct values in it, or calculate dynamic sums across filtered tables or columns.
-
DAX functions include a variety of time intelligence functions. These functions let you define or select date ranges, and perform dynamic calculations based on these dates or range. For example, you can compare sums across parallel periods.
When creating a formula, it is important to understand the difference between calculated columns and measures. In a calculated column, the same formula is applied to every row in the column, and the formula checks the values in each row, as well as any constants or related values, to calculate the result. For example, the formula might be as simple as =[ThisRowAmount] * 0.5. Even if the table is filtered, the formula applies to all rows, and filtering does not affect the results.
In a measure, however, filters and other forms of context change the values that are used in calculation, and thus affect the results. In a PivotTable, context is determined by the choice of row and column headings, and relationships between tables. If you create a formula that calculates the sum of sales as a measure, the result of the formula when placed in a PivotTable would be dictated by the groupings that you add to the PivotTable. For example, the same formula would yield different sums if you grouped the values by [Product Category] and [Fiscal Year], vs. grouping them by [Calendar Month] and [Sales Territory].
Comparing DAX Functions and Excel Functions
The DAX function library is based on the Excel function library, but the libraries have many differences. This section summarizes the differences and similarities between Excel functions and DAX functions.
-
Many DAX functions have the same name and the same general behavior as Excel functions, but have been modified to take different types of inputs, and in some cases, might return a different data type. Generally, you cannot use DAX formulas in an Excel workbook without modification, and vice versa.
-
Formulas that you write in a PowerPivot workbook are generally portable to tabular models, and vice versa. The exception is models deployed in Direct Query mode, which are subject to some restrictions. For more information, see DirectQuery Mode (SSAS).
-
DAX functions never take a range of cells or a range as reference, but always take a column or table as reference.
-
DAX date and time functions return a datetime data type. In contrast, Excel date and time functions return an integer that represents a date as a serial number.
-
Many DAX functions either return a table of values or make calculations based on a table of values as input. In contrast, Excel has no functions that return a table, but some functions can work with arrays. The ability to easily reference complete tables and columns is a feature in business intelligence semantic models.
-
DAX provides lookup functions that are similar to the array and vector lookup functions in Excel. However, the DAX functions require that a relationship be established between the tables.
-
DAX formulas do not support the variant data type found in Excel. The data in a column is expected to always be of the same data type. When you import data into a model, if the data is not the same type, the VertiPaq engine implicitly converts the entire column to the data type that best accommodates all values
– MSDN
Bineesh 