The Data Analysis Expressions
The Data Analysis Expressions (DAX) language is used to build formulas and expressions in several Microsoft Products. It allows us to perform advanced calculations and queries on data through a library of functions and operators.
DAX formulas are essential for creating calculations and securing the data by using row level filters.
To create formulas, you can use the following steps when building a formula:
- A formula must begin with an equal sign (=).
- Enter your expression you want. Note that there is the AutoComplete feature that helps you enter a valid formula syntax by providing you with options for each element in the formula
- Check for syntax errors: tables and values are referenced correctly, all parentheses are closed and columns,
- Press ENTER to accept the formula.
To perform different calculations, DAX includes a set of functions over 200 functions to use in formulas. We can find:
- Date and time functions
- Filter functions
- Information functions
- Logical functions
- Math and Trig functions
- Other functions
- Parent and Child functions
- Statistical functions
- Text functions
DAX calculation formulas are used in measures, calculated columns, calculated tables, and row filters.
Measures are created by using the DAX formula bar in the model designer. They are dynamic calculation where the results depend on a context.
In measures, we can use different functions to add new calculation like the aggregation functions (SUM, AVG, COUNT…), Date functions (…), Text functions (…)
For example, the formula below calculates the number of values in the column, Product.
Count_Measure := COUNT([Product])
Calculated column is used to add a new column to an existing table in the model. After adding the column with a valid DAX formula, values are calculated for each row. For example, we can add a new column that combines two fields in a table:
Full_Name = [First Name] & [Last Name]
Column values are only recalculated if the table or any related table is processed (refreshed) or the model is unloaded from memory and then reloaded, for example when closing and reopening a Power BI Desktop file.
Calculated table is used to add a standalone table, based on either a DAX query or formula expression, and that is available for queries, as fully operable as any other table.
Just like normal tables, relationships can be created with calculated tables. The columns in the calculated tables have a data type which can also be formatted.
An example of Calculated tables is to add a date table using DAX formula:
Table = CALENDAR (DATE (2000, 1, 1), DATE (2025, 12, 31))
Context enables you to perform dynamic analysis, in which the results of a formula can change to reflect the current row or cell selection and also any related data.
Mainly there are three types of context in DAX,
When you create a formula in a calculated column, the row context for that formula includes the values from all columns in the current row. If the table is related to another table, the content also includes all the values from the other table that are related to the current row. The Row context can be thought of as "the current row".
Query context refers to the subset of data that is implicitly retrieved for a formula. In a PivotTable, the query context can be changed by adding or removing column and row headers and by adding or removing Slicers.
Filter context is the set of values allowed in each column, or in the values retrieved from a related table. It is added when you specify filter constraints in the designer, or in the reports and PivotTables or by using formula.
When you create a DAX formula, the formula goes through two validation steps:
- Validate the syntax
- make sure the names of the columns and tables included in the formula can be found in the current context
An error is returned if the validation fails