Introduction to DAX in Excel May 27th, 2013

Vinod Kumar

DAX (Data Analysis Expression) was first introduced with PowerPivot as part of SQL Server 2008 R2 release. It is an expression language following the similar syntax of Excel formula, enabling Excel power users and BI developers the ability to write calculations inside PowerPivot workbook to extend PowerPivot’s analytical capability. DAX can be used to create a calculated column inside a PowerPivot table or a calculated measure dynamically evaluated inside the PivotTable against the PowerPivot data source. The details and implementation of this will be for future blog posts. In this blog post:

  1. Introduce DAX
  2. Enhancements with SQL Server 2012
  3. Functions available with DAX
  4. How are they different from Excel Functions
  5. How are they different from MDX functions

In V1 release of SQL Server 2008 R2, DAX supports functions which perform:

  1. Common mathematic and statistical functions, such as CEILING(), FLOOR(), ABS(), AVERAGE(), MIN(), MAX()
  2. Logical functions, such as IF(), TRUE(), FALSE()
  3. Informational functions, such as ISERROR(), ISBLANK()
  4. Date and Time functions, such as YEAR(), DATE(), MONTH()
  5. Filter function, such as FILTER()
  6. Functions that handle relationship of the tables, such as RELATED(), RELATEDTABLE()
  7. Functions that control the current context of the evaluation, such as VALUES(), EARLIER(), EARLIEST(), CALCULATE().
  8. Time Intelligence functions to allow analysis over time, such as PARALLELPERIOD(), CLOSINGBALANCEMONTH(), OPENNINGBALANCEMONTH()
What is new in SQL Server 2012

SQL Server 2012 has enhanced DAX functionality and removed some of the limitations from the V1 release to meet the following goals:

  1. Extend the functions available with SQL Server 2008 R2 release. These new functions not only expand the analytical capabilities, but also make authoring DAX calculations easier.
  2. Supports Power View report which uses BISM tabular database as the backend. This includes the new table queries, parameters and variables.

The list below offers a quick glance on the new functions in SQL Server 2012. The details on these functions will be covered in the later blogs.

Filter Functions
  1. HASONEFILTER
  2. HASONEVALUE
  3. ISCROSSFILTERED
  4. ISFILTERED
  5. ALLSELECTED
Informational Functions
  1. CUSTOMDATA
  2. CONTAINS
  3. LOOKUPVALUE

The following new functions are added to create parent child relationship: 

  1. PATH
  2. PATHCONTAINS
  3. PATHITEM
  4. PATHITEMREVERSE
  5. PATHLENGTH
  6. USERNAME
  7. USERELATIONSHIP
Logical Functions
  1. SWITCH
Math Functions
  1. CURRENCY
Statistical Functions
  1. ADDCOLUMNS
  2. CROSSJOIN
  3. DISTINCTCOUNT
  4. GENERATE
  5. GENERATEALL
  6. ROW
  7. STDEV.P
  8. STDEV.S
  9. STDEVX.P
  10. STDEVX.S
  11. SUMMARIZE
  12. TOPN
  13. VAR.P
  14. VAR.S
  15. VARX.P
  16. VARX.S
Basic syntax of DAX

A DAX expression always begins with an equal sign followed by the formula. The formula can contain constant values, DAX functions or the combination of the two. For instance,

= 1.1 * SUM(‘FactInternetSale’[SaleAmount])

= ‘Customer’[FName] & ‘, ‘ & ‘Customer’[LName]

All DAX functions share the common syntax as below,

DAXfunctionName(argument1, argument2, ….)

Depending on the function, argument can be a table expression (that returns a table), a column name or an explicit value. Table name in DAX needs to be enclosed by the single quotation ‘table name ‘ if the table name contains a white space between the characters, a special character or a reserved word. Column name must always be enclosed in []. It is not required to precede column name with the table name as in ‘FactSales’[SaleAmount], but it is highly recommended to fully qualify the column name to avoid any potential ambiguity when resolving a column name.

Data type supported in DAX

The data type for a column can be:

  1. Text
  2. Float
  3. Whole number
  4. Currency
  5. Date
  6. Boolean (True/False).
Difference between DAX and Excel formula

Even though DAX follows the same syntax convention as the Excel formula, there are fundamental differences between the two.

  1. DAX will only work with PowerPivot workbook and PivotTable created from the PowerPivot workbook. DAX cannot be consumed by regular non PowerPivot workbook or non PowerPivot PivotTable. Similarly, the regular Excel functions that are not implemented in DAX cannot be used in PowerPivot workbook or PowerPivot PivotTable.
  2. DAX is built upon the relational database concept, i.e. table and table relationship. Consequently, functions utilizing relationship provided in DAX. There are no such functionalities in Excel.
  3. DAX operates on entire table or entire column, i.e. DAX cannot operate on an individual cell, or a range/array of cells.
  4. To work with In Memory data, DAX has its own implementation on the common native Excel aggregation functions, such as SUM, AVERAGE, MIN, MAX, COUNT, which only takes column as the argument, unlike the native Excel functions which can take a list of ranges of cells as the function arguments.
  5. DAX has new aggregation functions that aggregates an expression over the rows of a table, such as SUMX, AVERAGEX, MINX, MAXX, COUNTX.
  6. DAX calculated measure provides dynamic aggregation based on the current filter context as defined by the members in the Row label, Column label, Value field, Slicers and Report Filter in the PivotTable.
  7. DAX provides Time Intelligence capabilities.
  8. DAX functions that returns a table can be used as the intermediate result to other DAX function that returns a single scalar value.
  9. The return data type may be different for the functions that share the same name in DAX and Excel, for instance, date and time functions return datetime data type in DAX. The same set of functions return integer in Excel.
  10. DAX supports several different data types such as Integer, Double, Datetime, String, Currency, Decimal, etc. In Excel, everything is either a number (double) or a string. Numbers are then formatted to appear as dates or as currency or with a fixed number of decimals. This use of formatting in Excel is different from having distinct data types as in PowerPivot.
Difference between DAX and MDX
  1. MDX is primarily a query language which asks for the specific data from the multi-dimensional Analysis Services database. MDX can also be used to create calculations in the traditional Analysis Services database, including calculated measures, calculated members, scope assignment, custom rollup etc.
  2. DAX is an expression language which allows Excel power user to create a calculated column in PowerPivot workbook or a calculated measure in Pivot table based on the PowerPivot workbook, using the familiar Excel formula syntax. DAX is not designed to replace MDX.
  3. MDX is based on multi-dimensional objects such as dimensions, measure groups, attributes, and hierarchies. MDX syntax includes notation for sets and tuples.
  4. DAX is based on relational constructs such as tables, columns, and relationships. DAX syntax is designed to mimic the syntax of Excel formulas.
  5. Inside PivotTable against PowerPivot data, Excel generates and sends MDX query to SSAS engine when user drags and drops members onto Row, Column, and Value fields.
  6. A DAX calculated measure or calculated column can only return one data type, while MDX treats measure as variant data type and one measure can return different data type based on the calculation condition. For example, inside the IF statement If (condition, expr1, expr2) in DAX, both expressions need to return the same data type, while in MDX, one expression can return a number and another returns a string.

Final Words

This is just a start to many more blog posts to come on DAX. Do let me know if you learnt something new today. I have seen customers ask these questions time and again when I show them Excel and DAX functionality. Thought this will be a dump of these conversations for reference to all. Thanks for reading this far.

Share this article

Tags: , , , , , , , ,

This entry was posted on Monday, May 27th, 2013 at 08:46 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply



 

Email
Print