Tweet
DAX stands for Data Analysis Expressions is designed to deliver easy-to-use constructs that allow extending the base PowerPivot model with calculations. With DAX you can define two types of calculations: (1) Calculated columns in PowerPivot Tables (2) Measures that define new fields in PowerPivot task pane.
If you’d like to follow a step-by-step tutorial on DAX, be sure to check out the labs here > Defining DAX Calculations with Microsoft PowerPivot for Excel 2010.
Let’s create some DAX calculations in this existing Excel 2010 report:
Go to PowerPivot>PowerPivot Window and note that we have the following tables with already extracted data from AdventureWorks SQL database:
[US Sales], [Product], [Category], [Subcategory], [Date], [US Geography], [US Population]
Add a new calculation DAX:=[SalesAmount] - [TotalProductCost]
Note support for intellisense in PowerPivot:
Create StateCode2 DAX as: =[StateCode]
Create a Relationship with US Population table:
Note: that PowerPivot does not support creating more than one relationship for a column. That’s why we are created a State2 DAX Calculation.
Go to US Population table and create a new DAX Calculation:=SUMX(RELATEDTABLE('US Sales'), 'US Sales'[SalesAmount]) / [Population]
Note: RELATEDTABLE function to retrieve all related rows based on the relationship created in the previous task. The SUMX function is then used to aggregate the SalesAmount column of the related rows. The aggregate result is then divided by the state’s population in order to compute the sales per capita.
To switch back to Excel:
Note that you need to click on the Refresh Button to see the new calculations:
Add GrossProfit to the report. In order to change the name and the formatting, right click on any cell under GrossProfit and choose “Value Field Settings”:
Change the name of the measure to Profit:
Apply a Number Format to Gross Profit to Comma Separated with Zero decimal places:
Lets apply some conditional formatting:
Now let’s add a new Measure to the PivotTable by going to PowerPivot ribbon and selecting “New Measure” and add the below DAX:=SUM('US Sales'[GrossProfit]) / SUM('US Sales'[SalesAmount]) . Note that Intellisense works inside the Measure Settings Dialog.
Rename Measure to Profit% and apply Percent (%) formatting:
Apply conditional formatting as we did earlier:
DAX stands for Data Analysis Expressions is designed to deliver easy-to-use constructs that allow extending the base PowerPivot model with calculations. With DAX you can define two types of calculations: (1) Calculated columns in PowerPivot Tables (2) Measures that define new fields in PowerPivot task pane.
If you’d like to follow a step-by-step tutorial on DAX, be sure to check out the labs here > Defining DAX Calculations with Microsoft PowerPivot for Excel 2010.
Let’s create some DAX calculations in this existing Excel 2010 report:
Go to PowerPivot>PowerPivot Window and note that we have the following tables with already extracted data from AdventureWorks SQL database:
[US Sales], [Product], [Category], [Subcategory], [Date], [US Geography], [US Population]
Add a new calculation DAX:=[SalesAmount] - [TotalProductCost]
Note support for intellisense in PowerPivot:
Create StateCode2 DAX as: =[StateCode]
Create a Relationship with US Population table:
Note: that PowerPivot does not support creating more than one relationship for a column. That’s why we are created a State2 DAX Calculation.
You can hide columns from showing in the Pivot Table by right clicking on the column and choosing Hide Columns>From PowerPivot:
Go to US Population table and create a new DAX Calculation:=SUMX(RELATEDTABLE('US Sales'), 'US Sales'[SalesAmount]) / [Population]
Note: RELATEDTABLE function to retrieve all related rows based on the relationship created in the previous task. The SUMX function is then used to aggregate the SalesAmount column of the related rows. The aggregate result is then divided by the state’s population in order to compute the sales per capita.
To switch back to Excel:
Note that you need to click on the Refresh Button to see the new calculations:
Add GrossProfit to the report. In order to change the name and the formatting, right click on any cell under GrossProfit and choose “Value Field Settings”:
Change the name of the measure to Profit:
Apply a Number Format to Gross Profit to Comma Separated with Zero decimal places:
Lets apply some conditional formatting:
Now let’s add a new Measure to the PivotTable by going to PowerPivot ribbon and selecting “New Measure” and add the below DAX:=SUM('US Sales'[GrossProfit]) / SUM('US Sales'[SalesAmount]) . Note that Intellisense works inside the Measure Settings Dialog.
Rename Measure to Profit% and apply Percent (%) formatting:
Apply conditional formatting as we did earlier:
Looking for some hands on practice without installing the tools? Check out SQL Server Virtual Labs: http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Wondering what is PowerPivot, check out: http://www.microsoft.com/en-us/bi/powerpivot.aspx
