Calculations in PowerPivot
PowerPivot doesn’t use Excel’s formulas or functions but uses its own special expressions, known as DAX (Data Analysis Expressions).
DAX Functions are different from Excel Functions in the following ways:-
DAX doesn’t reference single cells or ranges of cells (e.g. B1 or C2:D11). A DAX function always references a complete column or a table. If you want to use only particular values from a table or column, you add filters to the formula.
DAX includes functions that return an entire table as their results, rather than a single value e.g. the ALL function or DISTINCT function. These functions can only be used to provide input to other functions i.e. they can only be nested into other functions.
There are a group of DAX functions that provide time intelligence. These let you create calculations from the current date, month, period or year to compare the results across parallel periods in the past.
Where to Use DAX
You can use DAX formulas:-
In PowerPivot tables in the PowerPivot window where you can create calculated columns. You do this by adding a column to a table and then typing an expression in the formula bar. In a calculated column, the formula is always applied to every row in the column, throughout the table.
In the Values area of the PowerPivot Field list within an Excel workbook. These calculations are called measures and always perform some kind of aggregation on the source data, dependent on the data filtered by the Row or Column labels (or slicers etc.)
Creating Calculated Columns
To create a calculated field, first double-click into the column heading (labelled “Add Column”) to give the column a name. Then click into the first cell of the new column and then, just as in Excel, start your formula with an “=” (you don’t need to click into the formula bar). Type your formula and press Enter to accept. The formula in the column is computed for each row of the column
The PowerPivot window, like Excel, provides a formula bar to make it easier to create and edit formulas, and has an AutoComplete functionality to minimize typing and syntax errors.
Simple DAX formulas for Calculated Columns
To refer to a column in your current table, type a square bracket and then type the column name or click into a column in the current table.
Looking Up Values from Related Tables
Firstly, you’ll need to know how to refer to a column from another table. In the formula bar or in the first cell of a column, begin by typing the name of the table.
The AutoComplete provides a dropdown list containing valid names that begin with those letters. To refer to a column from another table, type the name of the table followed by the column name in square brackets:-
E.g.: WineSales[WineID] or Customers[CustomerID]
(Note: If the name of the table contains spaces, surround the name with single quotes)
For instance, let’s consider our tables again; Customers and WineSales. As explained earlier, the CUSTOMERID field in WineSales table is related to the CUSTOMERID field in the Customers table. Because of this relationship, we can easily bring any data from the Customers table into the WineSales table. For example, the CUSTOMERNAME field from the Customers table can be linked into the WineSales table. To do this we need to use the RELATED function.
Use the RELATED function to return a matching value from a related table. You can think of the RELATED function like the VLOOKUP function in Excel but a lot more powerful. The RELATED function has only one argument, the column name (including the table name) of the column you want from the related table.
In our example the function in the WineSales table would look like this:-
Other Useful Expressions
Unlike Excel, DAX does not allow a mix of data types in a column. So, for instance, this would return an error:-
Use the BLANK() function to return a blank e.g.:-
Use ISBLANK to search for blanks:-
The AND and OR functions in DAX only support 2 arguments. Use the AND operator (&&) and the OR operator (||) instead.
=if([SALESPERSONID]=6 && [WINEID]= 4 &&[CASESSOLD]>20,"Y","N")
=if([SALESPERSONID]=6 || [WINEID]= 4 ||[CASESSOLD]>20,"Y","N")
OK, that's lots of calculations to play with, try some of these and next time we'll start to look at "Measures", calculations in the Values area of our Pivot Table.