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

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

EVALUATE <Table Expression>

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

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

EVALUATE example

EVALUATE
  'Sales Territory'
 ORDER BY
    'Sales Territory'[Sales Territory Group] ASC,
     'Sales Territory'[Sales Territory Country] ASC,
     'Sales Territory'[Sales Territory Region]
 START AT
  "North America",
  "United States"

EVALUATE and FILTER

EVALUATE
 FILTER (
     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.

CALCULATETABLE

EVALUATE 
CALCULATETABLE ( 
     'Sales Territory', //Executed Third
     [Sales Territory Country] = "United States" //Executed Second
 )
ORDER BY 'Sales Territory'[Sales Territory Country] //Executed 1st

ADDCOLUMNS

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

SUMMARIZE

EVALUATE
 SUMMARIZE(
    'Internet Sales',
    'Internet Sales'[Order Date],
    "Sales Amount", 
             SUM ( 'Internet Sales'[Sales Amount] )
)

SUMMARIZE with ROLLUP

EVALUATE
 SUMMARIZE( 
     'Internet Sales',
    ROLLUP ( 'Date'[Calendar Year] ),
    "Sales Amount", 
             SUM('Internet Sales'[Sales Amount]) 
 )

CROSSJOIN

EVALUATE
 ADDCOLUMNS ( 
     CROSSJOIN ( 
         DISTINCT ( 'Product'[Color] ), 
         DISTINCT ( 'Product'[Size] ) 
     ),
     "Products",
        COUNTROWS ( RELATEDTABLE ( Product ) ) 
 )

Query measures

DEFINE
    MEASURE 'Product Category'[Subcategories Count] =
         COUNTROWS ( RELATEDTABLE ('Product Subcategory' ) )
     MEASURE 'Product Category'[Products Count] = 
         COUNTROWS ( RELATEDTABLE ('Product' ) )
 EVALUATE
ADDCOLUMNS ( 
     '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:

• Learn DAX Basics in 30 Minutes – Excel – Office Support
• Data Analysis Expressions (DAX) in Power Pivot