What is Power Bi DAX and How it works?

Power BI DAX

DAX (Data Analysis Expressions) is a formula expression language. It can be used in different BI and visualization tools. DAX is also known as function language in which the full code is kept inside a function. DAX programming formula contains two data types such as Numeric and Other .

Numeric includes currency, integers, and decimals, where Other includes string and a binary object.

Power BI DAX

How does it work?

For understanding the Power BI DAX, it has main three fundamental concepts such as:

  • Syntax
  • Context
  • Functions

1. Syntax

the syntax consists of various components that make up a formula.

Total Sales = SUM (Sales [SalesAmount])

  • Total Sales is the measure name.
  • The equal sign (=) operator indicates the beginning of the formula.
  • The DAX function SUM adds up all the numbers in the Sales[SalesAmount] column.
  • Parentheses () surround an expression containing one or more arguments. And all function requires at least one argument. An argument passes a value to a function.
  • The reference table Sales.
  • The referenced column [SalesAmount] in the Sales table. With this argument, the SUM function knows on which column to aggregate a SUM.

2. Context

Context is one of the essential concepts of DAX. It is categorized into two parts; Row context and Filter context.

The Row-Context is the easiest thought of as the current row. It applies whenever a formula has a function which uses the filters to identify a single row in a table.

The Filter context is a little more challenging to understand than the Row context. You can most easily think of the Filter-Context as one or more filters applied in a calculation. The Filter-Context doesn’t exist in the Row-context’s stead. Instead, it uses in addition to the former. Look at the following DAX formula.

3. Functions

Functions are predefined and ordered formula. They can perform calculations using arguments passed on to them. These arguments can be text, numbers, logical values, or other functions.

Types of Functions

Here are some important DAX functions:

1. Aggregate Functions

  • MIN
    This DAX function returns the minimum numeric value in a column, or between the two scalar expressions.




  • MAX
    This DAX function returns the maximum value in a column, including any logical values and numbers represented as text.


    This DAX function returns the arithmetic mean of the values in a column.


> AVERAGE(<column>)


  • SUM
    This DAX function adds all the numbers in a column.




2. Count Function

    This DAX function is used to return the count of items in a column. If there are multiple numbers of the same thing, this function will count it as separate items and not a single item.




    This DAX function is used to return the distinct count of items in a column. If there are multiple numbers of the same thing, this function will count it as a single item.




3. Date time Function

  • DATE
    This DAX function returns the specified date in Date-Time format.


> DATE(<year>, <month>, <day>)


  • HOUR
    This DAX function returns the specified hour as a number from 0 to 23 (12:00 A.M. to 11:00 P.M.).




4. Logical Function

  • AND
    This DAX function performs logical AND(conjunction) on two expressions. For AND to return true, both conditions specified have to be fulfilled.


AND(<logical argument1>,<logical argument2>)


  • OR
    This DAX function performs logical OR(disjunction) on two expressions. For OR to return true, either of the two conditions specified has to be fulfilled.

OR(<logical argument1>,<logical argument2>)

  • NOT
    This DAX function performs logical NOT (negation) on given expression.

NOT(<logical argument>)

5. Text function

    This DAX function joins two text strings into one text string.

CONCATENATE(<text1>, <text2>)

    This DAX function rounds a number to the specified number of decimals and returns the result as text.

FIXED(<number>, <decimals>, <no_commas>)

    This DAX function replaces part of a text string, based on the number of characters you specify, with a different text string.

REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)

Calculated Columns and Measures

The Power BI DAX formulae are used in calculations, in Measures and Calculated Columns.

Calculated Columns

When you create a data model on the Power BI Desktop, you can extend a table by creating new columns. The content of the columns is defined by a DAX expression, evaluated row by row or in the context of the current row across that table.


There is another way of defining calculations in a DAX model, useful if you need to operate on aggregate values instead of on a row-by-row basis. These calculations are measures. One of the requirements of DAX is a measure that needs to be defined in a table. However, the action does not belong to the table. So, you can move a measure from one table to another one without losing its functionality.