PowerPivot DAX

When defining your PowerPivot model in Excel 2010, you define data sources and relationships. The data is actually imported into the workbook and, just as in native Excel, you can define your own expressions for calculations using a special new syntax called Data Analysis Expressions (DAX).

 

DAX is designed to extend the normal Excel function set to provide powerful dynamic aggregation capabilities.  A simple example might be a Margin calculation as the difference between the Cost and the SalesAmount:

=[SalesAmount]-[TotalCost]

The similarity with Excel expressions allows current Excel users to use their existing skills while providing simplicity of the user interface.

A more advanced example shows aggregation of a “measure” TotalSalesAmount across a “dimension” DimChannel using the relationship between the DimChannel and the FactSales tables.

=SUMX(RELATEDTABLE(FactSales),FactSales[SalesAmount])

SUMX is a new DAX function explicitly for this purpose. RELATEDTABLE generates a table of aggregation based on the stated measure. Measures and Dimensions are standard objects defined in a relational Data Warehouse or a Multi-dimensional “Cube”. The power here is that the same DAX expression can be made on any table that has a relationship with the FactSales table giving aggregation by all the rows in that dimension table.

For more information on DAX, take a look at this sample and associated document:

http://www.microsoft.com/downloads/details.aspx?familyid=1AE63BFB-C303-44E3-AE44-7413D499495D&displaylang=en

Cheers

Brian

Related Courses

Implementing and Maintaining SQL Server 2008 Analysis Services and Business Intelligence Solutions (M6234)

SQL Server 2008 for Administration (M6231, M6232)

In this article

Join the Conversation