PowerPivot – Part 3: Basic DAX

PowerPivot is a combination of Excel and Analysis Services, enabling users to create complex and high-data-volume reports and enjoy the flexibility and usability of Excel alongside the high-performing SSAS engine. In this post I’ll be taking a closer look at a couple of DAX expressions. The DAX expression language is a special expression language which was added to the PowerPivot the Excel 2010 products, and enables the user to query multiple tables (as opposed to the traditional Excel expression language).

I’m going to use two new DAX functions, the RELATED() and the RELATEDTABLE() functions, to illustrate the very basic capabilities of the DAX expression language.

The RELATED function will link data from a table to another table, using the relationships created when the PowePivot model was created. We touched on Table Relationships in a previous blog post. So ,for example, If I want to add a calculated measure to the FactInternetSales fact table which displays the average yearly income of customers who bought specific products, I'll need to first add a new column to the FactInternetSales sheet and I’ll be using the following DAX expression to retireve the Yearly Income data from the DimCustomer table: RELATED(‘DimCustomer’[Yearly Income]). Next, I’ll add this as a measure to the Excel PivotTable report and change the aggregate function to AVERAGE, replacing the default SUM function. This is the end result of the report:

The new RELATEDTABLE() function returns a table, as opposed to a cell. There are several scenarios where you would want to use this function, specifically when you would want to show aggregations of measures from a table, but looking at a different, related table. For example, if you want to display the total sales for each customer, you could use this function to display this figure using the following expression: =SUMX(RELATEDTABLE('FactInternetSales'), 'FactInternetSales'[SalesAmount])

Note the usage of the new DAX function SUMX. It performs a summation of values, but unlike the SUM function which expects to receive a list of values, the SUMX function expects to receive a table as an input and an expression. Here’s how the new calculated column will look like:

-Yaniv

(This is post number 9 for week number 8 of my SQL blog challenge)