PowerPivot and Data Analysis Expressions (DAX)

The Data Analysis Expressions (DAX) language is a formula based language designed to help with custom calculations in PowerPivot tables. It resembles existing Excel formulas and it is designed to work with relational data. There are three main components to the DAX formula

 

  1. Functions – perform operations such as adding values, summing or performing logical tests
  2. Operators – determine how values are going to be compared or calculated (+, -, *, <, >)
  3. Values – data obtained from columns, tables. It cannot reference individual cells through.
  4. Expression – defines a literal or Boolean value.

However, there is one main difference between regular Excel formulas and DAX functions. The DAX functions utilize tables and columns in contrast to Excel formulas that rely on ranges. Its power comes from capability to perform complex lookups, aggregation and data retrievals.

The DAX language utilizes in-memory engine and due to this fact, it can analyze very large amounts of data unlike Excel formulas, which become progressively slow as you add more data to Excel file.

There are several functions that DAX exposes to use.

 

  1. Date and Time
  2. Filter
  3. Information
  4. Logic
  5. Math
  6. Statistical
  7. Text
  8. Time Intelligence

The DAX functions can return tables as its results versus one value only in Excel formulas. The DAX functions capable to perform row by row calculations. It utilizes filter for retrieving particular values from the columns. There are a lot more other types of capabilities available in DAX.

DAX has AutoComplete capability. It will present a list of available functions which start with “r” if you type only one letter in the formula bar.

Examples of DAX

=[Total]*8.46

=’Product’[Category]

=SUMX(FILTER(Marketing,Region=”USA”),[MarketingExpense])

Featured pages

Sharepoint

Learn SharePoint integration with PowerPivot and start utilizing enterprise grade software for comp…

Excel

Learn how to use PowerPivot in Excel and gain insights into your data. PowerPivot for Excel helps …

SQL

Learn about little known PowerPivot tips and tricks and make your Excel PowerPivot Business Intell…

Intelligence

Learn PowerPivot Business Intelligence capabilities and perform BI tasks on large data sets using …