DAX Basics

By the end of this lesson, you will understand the fundamental difference between DAX Measures and Calculated Columns in Power BI and be able to write basic aggregation formulas.

What it is

DAX (Data Analysis Expressions) is a formula language used in Microsoft Power BI, Analysis Services, and Power Pivot. It allows users to create custom calculations on data models. The two primary objects you will encounter are Calculated Columns and Measures.

A Calculated Column adds a new column to an existing table. Its value is computed row-by-row during data refresh and stored physically in the model. A Measure is a dynamic calculation that evaluates only when placed in a visual (like a chart or table). It does not store values; instead, it computes results based on the current filter context.

Related terms include Filter Context (the set of filters applied to a visual) and Row Context (the specific row being evaluated in a calculated column).

Why it matters

• Performance: Measures reduce file size because they do not store pre-computed values for every row.
• Flexibility: Measures automatically adjust their results based on user interactions (slicers, drill-downs), whereas columns remain static.
• Accuracy: Using measures ensures aggregations like sums and averages respect the current view's filters, preventing misleading totals.
• Reusability: A single measure can be used across multiple visuals without duplicating logic.

Syntax or steps

The basic syntax for both involves functions and references to tables or columns. However, the creation method differs slightly in the interface.

1. Calculated Column: Go to the Data View, select "New Column," and write a formula referencing the current row using [ColumnName].
2. Measure: Go to the Report View or Model View, select "New Measure," and write a formula using aggregate functions like SUM(), COUNTROWS(), or DIVIDE().

Note: In measures, you typically reference entire columns (e.g., 'Sales'[Amount]) inside aggregate functions. In columns, you reference the current row's value directly.

Example

// 1. Calculated Column: Creates a static label for each row
Category Label = 
IF(
    'Products'[Price] > 100, 
    "Premium", 
    "Standard"
)

// 2. Measure: Calculates total sales dynamically
Total Sales = 
SUM('Sales'[Amount])

// 3. Measure: Calculates average price per product
Avg Price = 
DIVIDE(
    SUM('Products'[Price]), 
    COUNTROWS('Products')
)

Explanation:

• The Category Label column evaluates once per row. If a product costs $150, that row permanently stores "Premium". This increases memory usage but allows filtering by text labels.
• The Total Sales measure sums the Amount column. If you slice the report by "Year 2023", the measure recalculates to show only 2023 sales. It stores no data itself.
• The Avg Price measure uses DIVIDE instead of / to handle division by zero errors gracefully, returning blank instead of an error.

Common mistakes

• Using Measures in Calculated Columns: You cannot reference a measure inside a calculated column because the column lacks the necessary filter context at creation time. Use explicit aggregations or variables instead.
• Forgetting Table Names: Always prefix column names with the table name (e.g., 'Sales'[Amount]) to avoid ambiguity if multiple tables have similar column names.
• Overusing Calculated Columns: Creating many calculated columns bloats the data model. If you only need a value for display or aggregation, use a measure.
• Ignoring Filter Context: Users often expect a measure to sum all rows regardless of slicers. Remember that measures respect the visual's filters unless you explicitly remove them using functions like ALL().

When to use it

Practice

Guided Exercise: Create a measure named Product Count that counts the number of unique products in the 'Products' table.

Hint: Use the COUNTROWS() function on the table.

Challenge: Write a calculated column called Discount Flag that returns "Yes" if 'Sales'[Quantity] is greater than 10, otherwise "No".

Expected Output: A new column in the Sales table with binary text values.

Quick check

Question: Why should you prefer a Measure over a Calculated Column for calculating Total Revenue?

Answer: Because a Measure respects the current filter context (such as date ranges or region selections) and does not consume storage space, ensuring accurate and efficient reporting.

Summary

DAX distinguishes between static row-level data (Calculated Columns) and dynamic aggregate calculations (Measures). Understanding this separation is critical for building performant and interactive Power BI reports. Use columns for categorization and measures for analysis.