1. Power Pivot and DAX: The Data Model

Power Pivot keeps several tables in a data model and links them like a database. Millions of rows, and no VLOOKUPs between sheets.

The star schema: in the middle sits a fact table (sales: date, product, store, amount), and around it the lookup tables, or dimensions (products, stores, calendar). A "one-to-many" relationship runs from the key of a lookup table to the fact table.

DAX is the formula language of the model. The key difference:

• a calculated column is computed for every row and stored in the model;
• a measure is computed "on the fly" for every cell of a PivotTable, using that cell's filters: Revenue := SUM(Sales[Amount]).

CALCULATE(expression, filters) is the key function: it changes the filter context. Chicago Revenue := CALCULATE([Revenue], Stores[City] = "Chicago"). SUMX(table, expression) is an iterator: it evaluates the expression row by row and adds the results up.

Availability: the full Power Pivot is in Excel for Windows; on Mac and on the web you can only use a model in a limited way.

Next in this track comes the Power Pivot simulator: a model of four tables and DAX measures that are calculated right in the browser.