📊

DAX Formulas and Power BI

Jul 14, 2025

Overview

This lecture introduces writing DAX formulas in Power BI and Excel, covers where to find DAX functions, explains contexts, and demonstrates practical examples creating and using measures.

Introduction to DAX and Power Pivot

  • DAX stands for Data Analysis Expressions, used for advanced calculations in Power BI and Excel.
  • Power Pivot combines elements of Excel PivotTables and Microsoft Access, allowing relationships between multiple tables.
  • DAX formulas are used in measures, calculated columns, calculated tables, and row-level security.

Finding and Using DAX Functions

  • Over 250 DAX functions exist; a full list is available online.
  • Not all functions in Power BI are available in Excel; if Intellisense does not recognize a function, it is not supported.
  • Workarounds are available if specific functions are missing in Excel.

Writing DAX Measures (Power BI & Excel)

  • Measures analyze data in visuals (Power BI) or PivotTables (Excel).
  • In Power BI, measures can be created via Home, Modeling, or Table Tools tabs.
  • In Excel, measures are created through Power Pivot’s "Measures" options or directly in the data model.
  • DAX formula syntax is similar to Excel formulas.
  • Example: SUM(Forecast[Forecast]) creates a measure summing forecast values.

Filter Context and Dynamic Formulas

  • Filter context refers to filters applied by visuals or PivotTables that affect measure calculations.
  • One DAX formula can yield different results depending on the current filter context (e.g., breaking down forecast by IT area).
  • Filter context makes DAX formulas dynamic and responsive to user selections.

Implicit vs. Explicit Measures

  • Explicit measures are written by the user; implicit measures are auto-created when fields are dragged into value areas.
  • Both types of measures respect filter context.
  • Aggregation methods can be changed for implicit measures (e.g., sum, average).

Advanced Measure Example: Percentage of Total

  • More complex measures may nest multiple DAX functions.
  • Example: Calculating "Forecast as a Percentage of Total Budget" using CALCULATE, ALL, and DIVIDE functions.
  • Referencing measures and using ALL removes filters, allowing calculation of totals across groups.

Key Terms & Definitions

  • DAX (Data Analysis Expressions) — Formula language for Power BI and Excel Power Pivot.
  • Power Pivot — Excel add-in for advanced data modeling and analysis.
  • Measure — A DAX formula used to perform calculations on data, shown in visuals or PivotTables.
  • Filter Context — The set of filters that determine what data a DAX formula operates on.
  • Implicit Measure — An automatically generated measure created by dragging a field into a value area.
  • Explicit Measure — A user-written measure using a DAX formula.
  • CALCULATE — DAX function to modify filter context.
  • ALL — DAX function that removes filters for specified columns or tables.
  • DIVIDE — DAX function for division that handles divide-by-zero errors.

Action Items / Next Steps

  • Download the lesson file from the provided link.
  • Practice creating measures in both Power BI and Excel.
  • Experiment with filter context in PivotTables and visuals.
  • Review the list of available DAX functions and try different aggregation options.

Full transcript