What are Data analysis expressions DAX with Examples

What are Data analysis expressions DAX with Examples

What are Data analysis expressions DAX with Examples

DAX is an expression language from Microsoft comprising of a collection of functions, operators, and constants that can be used in a simple excel based formula, or an expression. The only purpose of DAX is to calculate and return one or more values. DAX should not be confused as a programming language. Think of DAX as a formula language. You can use DAX to define custom calculations, calculated columns and measures. DAX helps you create new information and data insights from data already present in your data model. It is similar to MDX, but not exactly the same. In this post, I am going to talk about What are Data analysis expressions DAX with Examples.

DAX was first introduced with SQL Server 2012 Analysis Services Tabular Model, along with PowerPivot for Excel Add-In. It was released alongside SSAS Multidimensional which still uses MDX. The Tabular Data Model is a new engine with DAX in its core data modelling strategy.

DAX is designed to be
  • Simple
  • Fast
  • Not necessarily easy

Understanding How DAX Formulas Work

With the Introduction of DAX as an expression language, it introduced new concepts, such as:

  • Calculated Columns, Measures
  • Filter Context / Evaluation Context

These formulas are very similar to Excel formulas and just like regular Excel based formulas, you type in an equal sign, followed by a function name or expression, with additional required values or arguments. Just like Excel, DAX provides a variety of functions that you can use to work with different data types, such as strings, perform calculations using dates and times, or create conditional values.

With the new Tabular Engine, there was a need for a new Language better fit in the Data Modelling World.

How are DAX formulas different from Excel Formulas:

  • In case you want to customize calculations on a row-by-row basis, DAX provides functions that let you use the current row value or a related value from a different table to perform calculations that vary by context.
  • Using a DAX function you can return a table as its result, rather than a single value. The output of these functions can be used to provide input to other functions.
  • You can find and calculate data ranges using the Time Intelligence Functions in DAX. You can create calculations using date ranges, and do a comparison of the results across parallel periods just like MDX functions.
Where can you write DAX formulas

You can write DAX in applications with support the Tabular Model, such as Power BI Desktop, PowerPivot or Excel, SSAS Tabular Project in Visual Studio or SSDT. In these applications, you can write DAX in Calculated Columns, Measures.

Understanding the DAX Syntax


    { MEASURE <table>[<col>] = <expression> }]

EVALUATE <Table Expression>

[ORDER BY {<expression> [{ASC | DESC}]} [, …]

    [START AT {<value>|<parameter>} [, …]] ]

EVALUATE example


  'Sales Territory'

    'Sales Territory'[Sales Territory Group] ASC,
     'Sales Territory'[Sales Territory Country] ASC,
     'Sales Territory'[Sales Territory Region]

  "North America",

  "United States"


     FILTER ( 
         'Internet Sales',
         [Ship Date] > [Due Date]  
     'Internet Sales'[Unit Price] > 2 

It is an easy way to filter data, because the syntax of filter, you pass 2 arguments. In the above examples, Is a condition that has to be true for every row you want the filter to return.

In the second example, we can see that we have 2 nested filters. In this case, I am using Filter, The outer filter runs an iteration over the first argument. The first argument is a table expression.

Innermost Function is executed first. ‘Internet Sales'[Unit Price] > 2


Avoid Filter in DAX expressions from a performance perspective.



     'Sales Territory', //Executed Third
     [Sales Territory Country] = "United States" //Executed Second

ORDER BY 'Sales Territory'[Sales Territory Country] //Executed 1st



  'Internet Sales',
   "Margin", ([Sales Amount] - [Total Product Cost])





    'Internet Sales',

    'Internet Sales'[Order Date],

    "Sales Amount", 
             SUM ( 'Internet Sales'[Sales Amount] )



     'Internet Sales',

    ROLLUP ( 'Date'[Calendar Year] ),

    "Sales Amount", 
             SUM('Internet Sales'[Sales Amount]) 



         DISTINCT ( 'Product'[Color] ), 
         DISTINCT ( 'Product'[Size] ) 

        COUNTROWS ( RELATEDTABLE ( Product ) ) 

Query measures


    MEASURE 'Product Category'[Subcategories Count] =
         COUNTROWS ( RELATEDTABLE ('Product Subcategory' ) )
     MEASURE 'Product Category'[Products Count] = 
         COUNTROWS ( RELATEDTABLE ('Product' ) )

     'Product Category',
     "SubCategories", [Subcategories Count],
     "Products Count", 'Product Category'[Products Count] 

In DAX, only the result rows of the Query are fetched and transferred to the client application.

Official Documentation:

Related Posts

Best Practices for Configuring SQL on Azure IaaS V... Best Practices for Configuring SQL on Azure IaaS VM Running SQL Server workloads on Azure as IAAS (Infrastructure as a service) is becoming popular. ...
Free Top 5 HTML Editors for Windows 10 Free Top 5 HTML Editors for Windows 10 If you are experienced with HTML and want an advanced editor software  to create, design, modify and develop...
SQL Server Hybrid Approaches for High Availability SQL Server Hybrid Approaches for High Availability SQL Server High Availability technologies already gives you a lot of flexibility to fit diverse de...
How to see and Export All Installed Windows 10 Dri... All editions of Windows (Including Windows 10) contains a driver cache of signed device drivers with ".inf" files stored in the system folders. If you...
Disclosure: We are a professional review website that sometimes receive compensation or free units from the companies whose products we review. We test each product thoroughly and give high marks to only the very best. We are independently owned and the opinions expressed here are our own.