Article

The DAX language


A-Badde | Posted on April 03, 20

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

  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:

  1. A formula must begin with an equal sign (=).
  2. 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
  3. Check for syntax errors: tables and values are referenced correctly, all parentheses are closed and columns,
  4. Press ENTER to accept the formula.

DAX Functions

  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

Calculations

  DAX calculation formulas are used in measures, calculated columns, calculated tables, and row filters.

Measures

  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 columns

  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 tables

  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

  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,

Row context

  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

  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

  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


(1) Comment
Generic placeholder image
Countersunk Ndfeb Magnet Nov. 26, 20

Leave a Comment

Your email address will not be published. Required fields are marked *

Add to favorite
Categories
Recent Posts
DAX Language - CONTAINS Function
A-Badde | May 09, 20

The CONTAINS function returns a boolean value, TRUE if each ...Read More


DAX Language - RANKX Function
A-Badde | May 05, 20

The RANKX function returns a single integer value that repre...Read More


DAX Language - AND and OR Functions
A-Badde | April 10, 20

AND/OR functions check whether all arguments are TRUE or one...Read More


DAX Language - Variables in DAX
A-Badde | April 10, 20

Use Var keyword to store the result of an expression as a na...Read More