📊

DAX Formulas in Power BI and Excel

Jul 14, 2025

Overview

This lecture introduces DAX formulas in Power BI and Excel, covering basic formula writing, key concepts like context, and examples of creating and using measures.

Introduction to DAX and Power Pivot

  • DAX (Data Analysis Expressions) is a formula language used in Power BI and Excel Power Pivot.
  • DAX functions resemble Excel functions, making it easier for Excel users to learn.
  • DAX enables advanced calculations on data across related tables.
  • Power Pivot is a data modeling tool that combines features of Excel pivot tables and Access.

Writing DAX Formulas and Measures

  • Measures are reusable calculations in Power BI visuals or Excel pivot tables.
  • DAX formulas are written using the formula bar in Power BI (found on Home, Modeling, Table Tools, or measure ellipses).
  • In Excel, write measures via Power Pivot tab > Measures > New Measure, or in the Power Pivot window.
  • Excel measure syntax: measure name, colon and equal sign, then the formula (e.g., ForecastTotal := SUM(Forecast[Forecast])).
  • Measures appear in field lists indicated by a calculator/function icon.

DAX Functions Availability

  • Over 250 DAX functions exist; not all are available in both Power BI and Excel.
  • If a function (e.g., REMOVEFILTERS) does not show up with Intellisense in Excel, it is unavailable there.
  • Workarounds can be made using alternative functions if needed.

Understanding Filter Context

  • Filter context is how DAX determines which data to include based on filters or pivot table structure.
  • Measures automatically respect filter context, recalculating values as filters or fields are added.
  • In Power BI, slicers and filters modify filter context dynamically.

Explicit vs. Implicit Measures

  • Explicit measures are custom formulas written by users.
  • Implicit measures are created automatically when numeric fields are dragged to the values area.
  • Both types of measures honor filter context, updating with changes in filters or fields.

Creating More Complex Measures

  • More advanced DAX measures can nest multiple functions, such as CALCULATE, ALL, and DIVIDE.
  • CALCULATE modifies filter context to compute values like totals across all categories (e.g., ignoring certain filters).
  • DIVIDE is used to create percentage measures and avoid division by zero errors.

Key Terms & Definitions

  • DAX (Data Analysis Expressions) — Formula language for data modeling in Power BI and Excel Power Pivot.
  • Power Pivot — An Excel add-in for building data models and creating advanced calculations.
  • Measure — A reusable calculation written with DAX for use in visuals or pivot tables.
  • Filter Context — The set of filters applied to data that determines how measures are calculated.
  • Explicit Measure — Custom DAX formula written by the user.
  • Implicit Measure — Automatically created calculation when dragging a field to a values area.

Action Items / Next Steps

  • Download the lesson file from the provided link.
  • Practice writing basic and advanced DAX measures in Power BI or Excel.
  • Explore the complete DAX function list online for reference.

Full transcript