Advanced Excel · Intermediate level

Intermediate level · Module 2 of 6 · 16 guided hours

Data Models and Power Pivot

Relate several tables in a data model and write DAX measures for powerful, consistent analysis.

Learning objectives

  • Build a data model with related tables
  • Design star schemas with fact and dimension tables
  • Write DAX measures for analysis

Topics

  • data model
  • relationships
  • fact and dimension tables
  • star schema
  • DAX measures
  • CALCULATE and time intelligence basics

Lessons

  1. Lesson 1 Building a Data Model

    Load tables into the data model and connect them with relationships.

    data modelrelationshipkey columnone-to-many

  2. Lesson 2 Star Schemas

    Organise models into fact and dimension tables for clear, fast analysis.

    fact tabledimension tablestar schemadate table

  3. Lesson 3 DAX Measures

    Write DAX measures that calculate correctly in any PivotTable context.

    measurefilter contextCALCULATEDIVIDE

Practical activity

Model the NGO's distributions, locations, items and dates as a star schema and write measures for totals, averages and month-on-month change.

Expected outcomes

  • Models that combine tables without lookup formulas
  • Reusable, consistent measures

Assessment

Practical: build a model and five measures; marked by checks against SUMIFS calculations.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.