Skip to content
Tutorials Center
My learning

Topics

Lesson 64 of 93 ยท PivotTables and Data Analysis

The Data Model, Power Pivot and DAX Overview

Learn Excel's Data Model: relationships, one-to-many tables, measures vs calculated columns, basic DAX (SUM, CALCULATE, DISTINCTCOUNT) and when to leave pivots.

An ordinary PivotTable works on one flat list. Real data is rarely flat: orders live in one table, customers in another, products in a third. The classic fix is a column of VLOOKUPs that copies customer and product details into the orders list, which gets slow and heavy. Excel's Data Model is the alternative: you load several tables, tell Excel how they relate, and build one PivotTable across them.

The Data Model comes with Power Pivot (the add-in for managing it) and DAX, a formula language for calculations on it. Excel cannot run on this site, so the DAX and screen steps are read-only text. The TryIts use pandas merge and groupby as stand-ins to show the logic: a relationship is a join, a measure is a calculation done after filtering. They are not the real engine, and the real thing is much faster on big data.

What the Data Model gives you

Relationships Link tables by a key.
Millions of rows Compressed in memory.
Measures Reusable DAX calculations.
One PivotTable Fields from many tables.
1 / 2

Relationships and one-to-many

A relationship connects a column in one table to a column in another. The usual case is one-to-many: one customer places many orders.

  • The lookup table (the "one" side, e.g. Customers) has a unique key such as CustomerID.
  • The fact table (the "many" side, e.g. Orders) repeats that key in every row it belongs to.
erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    PRODUCTS ||--o{ ORDERS : "appears in"

One customer and one product can appear in many order rows; keys on the "one" side are unique.

To create one: keep each table as an Excel Table with a clear name, select a cell and choose Insert > PivotTable > From Data Model or tick Add this data to the Data Model when creating a pivot. Then open Data > Relationships and click New: pick the Orders table and its CustomerID column, pick Customers and its CustomerID column, and confirm. In Power Pivot > Manage > Diagram View you can also drag one key onto the other.

Now the PivotTable Fields pane shows every table. Put Customers[Country] in Rows and Orders[Amount] in Values: the pivot follows the relationship from the orders to each customer. No VLOOKUP column is needed.

A relationship needs matching data types, and the key on the "one" side must not contain duplicates; otherwise Excel can only offer a many-to-many relationship in Power Pivot or complains. Filters flow from the "one" side to the "many" side: picking a country filters orders, not the other way round.

Run it: a relationship is a join

Python Orders joined to customers, then grouped by country

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Measures versus calculated columns

Both use DAX but they work differently:

Calculated column Measure
Evaluated Once per row, when the data loads or refreshes On the fly, for each cell of the pivot, in its filter context
Stored Yes, takes memory No, it is only a formula
Used as Rows, Columns, Filters or Values Values only
Example =Orders[Qty] * Orders[Price] Total Sales := SUM(Orders[Amount])

Rule of thumb: if you want to slice by it, make a calculated column; if you want to add, average or compare it, make a measure. Create a measure in the Fields pane (right-click the table > Add Measure) or in Power Pivot's calculation area.

A first look at DAX

DAX looks like Excel formulas but works on columns and tables and respects the filters in the pivot (the filter context). Measures are written Name := formula in Power Pivot and Name = formula in the Excel dialog. The basics:

TEXT
Total Sales := SUM(Orders[Amount])
Orders Count := COUNTROWS(Orders)
Unique Customers := DISTINCTCOUNT(Orders[CustomerID])
Average Order := DIVIDE([Total Sales], [Orders Count])
Spain Sales := CALCULATE([Total Sales], Customers[Country] = "Spain")
Share of Total := DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Customers)))
  • SUM, COUNTROWS, AVERAGE, DISTINCTCOUNT aggregate a column or table.
  • DIVIDE divides and returns blank (or an alternative) instead of an error when the denominator is zero.
  • CALCULATE is the most important DAX function: it evaluates an expression after changing the filters. Spain Sales always shows Spain, whatever country is in the row.
  • ALL removes filters, so Share of Total compares with the grand total.

If you can write SUMIFS, you can learn CALCULATE: CALCULATE([Total Sales], Customers[Country]="Spain") plays the role of SUMIFS(Amount, Country, "Spain"), but works across related tables and respects the pivot's filters.

Cell formulas can read the model with CUBE functions, such as =CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Customers].[Country].&[Spain]"). Converting a pivot to formulas (PivotTable Analyze > OLAP Tools > Convert to Formulas) generates them.

Run it: measures in filter context

The stand-in below mimics measures as functions of the rows left after filters. CALCULATE replaces a filter; DISTINCTCOUNT counts unique keys. Note how Unique Customers differs from the number of orders.

Python Measures, CALCULATE and DISTINCTCOUNT stand-ins

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

When to leave ordinary PivotTables

Stay with a plain PivotTable on one Table when the data is one list and under a few hundred thousand rows. Move to the Data Model when:

  • You need fields from several tables without lookup columns.
  • You need distinct counts (unique customers, unique products), which ordinary pivots cannot do. Tick Add this data to the Data Model and use Value Field Settings > Distinct Count.
  • The data has more than about a million rows (the worksheet limit is 1,048,576 rows; the model can hold more when loaded from Power Query or a database without placing it on a sheet).
  • You want reusable calculations such as year-to-date or share of total that behave in every pivot.
  • You want data from Power Query loaded straight to the model; see Power Query: Get and Transform.

Also know the limits: DAX has a learning curve; Power Pivot is not included in every Excel edition (it is available in some Microsoft 365 and Office Professional Plus editions and not on every platform, such as parts of Excel for Mac and Excel for the web, so check your version); and when a workbook with a model is shared, everyone needs a compatible Excel version to edit it. When the analysis outgrows Excel, a database with SQL, or Power BI, which uses the same engine and DAX, is the next step.

The Power Pivot add-in, managing the model and Power View

Everything above works with the Data Model that every modern Excel can use behind a PivotTable. The Power Pivot add-in adds a full window for managing that model. Excel and Power Pivot cannot run on this site, so the steps below are read-only.

Enabling the add-in

Power Pivot ships with Excel for Windows in some editions (Microsoft 365 apps for enterprise, Office Professional Plus and several others) and is not in Excel for Mac or Excel for the web (which can read models but not edit them). To enable it: File > Options > Add-ins, set Manage: COM Add-ins, click Go, tick Microsoft Power Pivot for Excel, and click OK. A Power Pivot tab appears with a Manage button. If the entry is missing, your edition does not include it.

Loading data and the Power Pivot window

Click Manage to open the Power Pivot window. From here:

  • Get External Data loads from databases, text files and other sources (or load from Power Query; both end in the same model).
  • Each table is a tab at the bottom, and every column has a data type and formatting that you set here (decimal, date, currency, percentage).
  • Diagram View shows the tables as boxes joined by relationship lines; drag a column onto another table's column to create a relationship, and double-click a line to edit it or to turn it off. Only one relationship between two tables can be active at a time (relationships).
  • Calculation area (below the grid) is where you type measures, such as Total Sales := SUM(Sales[Amount]).

Keep the model small: remove columns you will never use (especially long text columns), because they cost memory.

Hierarchies and calculated columns

A hierarchy is a drill path such as Year > Quarter > Month, or Category > Subcategory > Product. In Diagram View, select the columns, right-click, choose Create Hierarchy, and name it. In a PivotTable, the hierarchy appears as one field and you can expand and collapse levels together. Calculated columns add a column to a table using DAX evaluated row by row; prefer measures for anything you aggregate (see "Measures versus calculated columns" above). Mark your date table with Design > Mark as Date Table so time-intelligence functions work.

Power Pivot charts

With a model loaded, Power Pivot > PivotChart (or Insert > PivotChart and choose the data model) builds a chart linked to a PivotTable on the model. A KPI can be created from a measure (Power Pivot > KPIs > New KPI) with a target and status thresholds, giving icons in the pivot. Slicers and timelines filter all the linked charts at once (Slicers and PivotCharts).

Power View: status

Power View was an interactive reporting sheet (Insert > Reports > Power View) introduced in Excel 2013. It was deprecated and removed from current Excel and replaced by Power BI, so you will only meet it in old workbooks. If you open one, the Power View sheets may not display in newer versions. The modern route is to publish your model to Power BI, or to build the dashboard with PivotTables, slicers and charts in Excel (Dashboards).

Analyze in Excel and the Power BI hand-off

The Data Model and Power BI share the same engine, which makes moving work between them practical:

  • Power BI Desktop > File > Import > Power Query, Power Pivot, Power View converts an Excel model into a Power BI report.
  • In the Power BI service, Analyze in Excel connects an Excel PivotTable to a published Power BI dataset, so you build pivots against a governed, shared model without copying data.
  • Inside Excel, Insert > PivotTable > From Power BI (where available) picks a dataset directly.

Choose Power BI when the model should be shared with many viewers; keep the workbook when only a few analysts work in Excel.

Run it: a hierarchy is a grouping you can roll up

JS Year, quarter and month roll-ups from one date table

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a one-to-many relationship between Customers and Orders, which table has the unique key?

    The "one" side (Customers) has one row per customer with a unique CustomerID. Orders repeats the ID on many rows.
  2. 2. Which of these should be a measure rather than a calculated column?

    Measures are evaluated per pivot cell in the current filter context, so they suit totals and ratios. Calculated columns are row-level values you can slice by.
  3. 3. What does CALCULATE do in DAX?

    CALCULATE evaluates its first argument with the filters you add or remove, similar in spirit to SUMIFS but across related tables.
  4. 4. Which task is hard for an ordinary PivotTable but available through the Data Model?

    Distinct Count appears in Value Field Settings only when the data is in the Data Model, and DISTINCTCOUNT is a standard DAX function.

Summary

  • The Data Model lets one PivotTable use several related Excel Tables, replacing columns of lookups.
  • A one-to-many relationship links a unique key in the lookup table to a repeated key in the fact table; filters flow from the "one" side to the "many" side.
  • Use calculated columns for row-level values you slice by and measures for aggregations; measures are evaluated in filter context.
  • Basic DAX: SUM, COUNTROWS, DISTINCTCOUNT, DIVIDE, CALCULATE and ALL.
  • Move beyond ordinary pivots for several tables, distinct counts, big data and reusable calculations, after checking that your Excel edition supports Power Pivot.

Next: What-If Analysis: Goal Seek, Scenarios and Data Tables.