Getting Started with DAX in Power Pivot

With PowerPivot in Excel 2010 you will find a fancy new formula language called Data Analysis Expression (DAX). DAX is used for extending what you can do with your PowerPivot data. This new expression language is considered an extension of the Excel formula language but it has the look of being a cross between the current Excel formula language and MDX.

DAX can be used to create calculated columns in the PowerPivot Window, essentially like a derived column in T-SQL. It can also be used to create calculated measures while browsing PowerPivot data in a pivot table.

To follow the examples used in this article you will need:

· Office 2010

· PowerPivot for Excel 2010

· The AdventureWorks samples databases

I will cover several commonly used DAX expressions but you can find a good reference for the rest on TechNet. This article is more focused on DAX so I’ll quickly get you to a point where you can start writing some code.

Start by opening Excel 2010 and selecting the PowerPivot tab, which should appear after installing the feature. On this tab, click PowerPivot Window to launch PowerPivot. Choose the From Database drop down and select From SQL Server.

This will open the Table Import Wizard where you will create the connection to the AdventureWorksDW2008 sample database. Connect to your instance which has this database then click Next. For this example pick Select from a list of tables and views to choose the data to import then click Next. Select the FactInternetSales table then click Select Related Tables, which will check all tables that have a relationship to this table. You should have 6 tables selected before you click Finish. You will then see each table get imported and once it completes you can click Close.

From here you can see each of the table listed as tabs in the PowerPivot Window. This has the same information that you will find in the SQL Server tables.

Let’s start with a basic DAX example. Let’s say we need to concatenate two fields together like first and last name. Click on the DimCustomer tab and select the column that says “Add Column”. When this is selected you can begin writing your DAX expression where you would typically write Excel formulas in the fx box.

Type in the following formula:

= [FirstName] & " " & [LastName]

The results will return back a new column that you can rename FullName with both first and last name concatenated together.

Next, click on the DimSalesTerritory tab where we will experiment with using the RELATEDTABLE function. Use the following formula template to return a total number of records for each record in the DimSalesTerritory table:

=SUMX(RELATEDTABLE(TableName),TableName[ColumnName])

Or using our example:

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

You can then rename the column whatever you would like. In this case let’s call it RegionInternetSales. RELATEDTABLE must be used with another function because it returns an entire table’s contents.

So far I have shown you calculated columns, but how do you create calculated measures? Calculated measures are actually created while viewing the PowerPivot data in a PivotTable. To do this, click the PivotTable drop down on the Home tab in the PowerPivot Window. Select PivotTable from the dropdown and then click OK when the Create PivotTable box opens. In the PowerPivot Field List pane right click on the FactInternetSales table and choose Add New Measure.

For this example you will write a calculation to show current period last year sales. Use this template to create the calculation then test the formula by clicking Check formula

=CALCULATE(SUM(TableName[ColumnName]),DATEADD(‘DateTable'[DateColumn],-1,YEAR),All(‘DateTable’))

Or using our example:

=CALCULATE(SUM(FactInternetSales[SalesAmount]),DATEADD(‘DimDate'[FullDateAlternateKey],-1,YEAR),All(‘DimDate’))

As soon as you hit OK it will apply it to the PivotTable. Make sure the formula is working by adding CalendarYear and CalendarQuarter on Row Labels and SalesAmount to the Values section.

To wrap up this short DAX tutorial let’s do one more calculated measure to return year to date. Right click on the FactInternetSales table and select Add New Measure again. Use this formula template to for the new calculated measure:

=CALCULATE(SUM(‘TableName'[ColumnName]),DATESYTD(‘DateTable'[DateColumn]),All(‘DateTable’))

Or using our example:

CALCULATE(SUM(‘FactInternetSales'[SalesAmount]),DATESYTD(‘DimDate'[FullDateAlternateKey]),All(‘DimDate’))