DAX Basics and Concepts

Overview

This lecture introduces the basics of DAX (Data Analysis Expressions), covering its use in Power BI, data types, calculated columns vs. measures, key DAX functions, and foundational table concepts.

Session Structure & Expectations

• The session is split into a practical morning (hands-on DAX formulas) and a theoretical afternoon (evaluation context).
• Most attendees are familiar with DAX basics; advanced concepts will be addressed.
• Interaction is encouraged: ask questions loudly anytime.

Introduction to DAX

• DAX is the formula language for Power BI, Analysis Services, and other Microsoft BI tools.
• DAX concepts are simple, but mastering the language requires time, especially understanding evaluation context.
• DAX is a functional language, similar to Excel formulas, using nested functions.

Data Types in DAX

• DAX is strongly typed: each column has a defined data type.
• Main numeric types: integer, decimal, currency, date/time, Boolean.
• Other types: string, binary (images, etc.).
• Type conversions can occur automatically but should be avoided for reliability.

Calculated Columns vs. Measures

• Calculated columns are computed at refresh time, stored in the model, and use row context (current row).
• Measures are computed at query/report time, respond to filters, and use aggregation functions (no row context).
• Measures are preferable for performance and flexibility unless a physical column is needed.

Aggregators & Iterators

• Simple aggregators: SUM, AVERAGE, MIN, MAX—work on single numeric columns.
• Iterators (e.g., SUMX, AVERAGEX): aggregate over expressions or multiple columns, iterating row-by-row.

Key DAX Functions

• COUNT, COUNTA, COUNTBLANK: count values/rows, with blank handling varying.
• COUNTROWS: counts rows in a table; most commonly used.
• Logical functions: AND, OR, NOT—use either Excel style or programming operators.
• Information functions: ISBLANK, ISNUMBER, ISTEXT, ISERROR—test data type or error status.
• DIVIDE: safely divides numbers, handling division by zero.
• Variables (VAR): store values or tables for reuse in expressions; improve performance and readability.

Working with Relationships & Table Functions

• RELATED: fetches a value from a related (lookup) table (many-to-one relationship).
• RELATEDTABLE: retrieves related rows from another table (one-to-many).
• FILTER: returns a table filtered by an expression.
• ALL: removes filters; used to compute grand totals or distinct values.
• DISTINCT/VALUES: return distinct values from a column, with slight differences in handling blanks.
• CALCULATED TABLES: DAX tables created by expressions, useful for pre-aggregation or prototyping.

Practical DAX Syntax Tips

• Always include table names when referencing columns for clarity.
• Formatting DAX code with indentation improves readability.
• Calculated columns do not respond to report filters, while measures do.

Key Terms & Definitions

• DAX (Data Analysis Expressions) — a formula language for data modeling in Power BI and related tools.
• Calculated Column — a column computed at data refresh, stored in the model, using row context.
• Measure — a dynamic calculation computed at query time, using aggregators and responding to filters.
• Iterator (e.g., SUMX) — a function that iterates over a table, evaluating an expression for each row.
• Row Context — the current row in a table during calculation.
• Evaluation Context — the context in which a calculation is performed (to be covered later).
• Related/RelatedTable — functions navigating relationships between tables.
• ALL — function that removes filters from a table or column.
• Variable (VAR) — a named value or table used within a DAX expression.
• Calculated Table — a table generated by a DAX expression within the model.

Action Items / Next Steps

• Practice creating calculated columns and measures in Power BI with the provided Contoso dataset.
• Review DAX formatting and syntax best practices.
• Read about evaluation context and prepare questions for the next session.
• Attend the afternoon lecture on evaluation contexts to deepen DAX understanding.