Introduction to DAX

What You'll Learn

• What DAX is and why you need it
• Calculated columns vs measures
• Basic DAX syntax
• Your first DAX formulas

What is DAX?

DAX (Data Analysis Expressions) is Power BI's formula language.

Think of it like Excel formulas, but more powerful!

Why Learn DAX?

• Create custom calculations
• Time-based analysis (YTD, MTD)
• Complex aggregations
• Advanced filtering
• Unlock Power BI's full potential

DAX vs Excel Formulas

Similar:

• Both use functions (SUM, IF, etc.)
• Both reference columns/cells
• Similar syntax structure

Different:

• DAX works on entire columns/tables
• DAX is context-aware
• DAX has special functions Excel doesn't

Calculated Columns vs Measures

Two main types of DAX calculations:

Calculated Columns

What: Add new column to existing table When created: During data refresh Storage: Takes up space in model Filters: Can be filtered/sliced

Example: Add "FullName" column combining First + Last

Use when:

• Static calculations
• Row-by-row operations
• Need to filter/slice by result

Measures

What: Dynamic calculation shown in visuals When created: At query time (real-time) Storage: No storage - calculated on demand Filters: Cannot be filtered (they ARE the result)

Example: Total Sales that changes based on filters

Use when:

• Aggregations (Sum, Average, Count)
• Dynamic calculations
• Context-aware results

Rule of thumb: Measures > Calculated Columns (use measures when possible!)

Basic DAX Syntax

Formula Structure

ColumnName = Expression or MeasureName = Expression

Referencing Columns

Use square brackets: [ColumnName] Include table name: TableName[ColumnName]

Example: Sales[Amount] Products[Price]

Operators

Math:

• (add)

• (subtract)

• (multiply) / (divide)

Comparison: = (equals) <> (not equals)

(greater than) < (less than)

Logical: && (AND) || (OR)

Text: & (concatenate)

Your First Calculated Column

Add FullName column:

1. Click table in Fields pane
2. Right-click > New Column
3. Formula bar appears at top
4. Type: FullName = [FirstName] & " " & [LastName]
5. Press Enter

Done! New column appears in table.

Your First Measure

Add Total Sales measure:

1. Right-click table > New Measure
2. Type: Total Sales = SUM(Sales[Amount])
3. Press Enter

Use in visuals! Drag to value field.

Basic DAX Functions

SUM

Add all values in column: Total = SUM(Sales[Amount])

AVERAGE

Calculate mean: Avg Sales = AVERAGE(Sales[Amount])

COUNT

Count rows: Order Count = COUNT(Orders[OrderID])

COUNTROWS

Count rows in table: Row Count = COUNTROWS(Sales)

MIN / MAX

Lowest/highest value: Min Price = MIN(Products[Price]) Max Price = MAX(Products[Price])

IF

Conditional logic: Category = IF([Amount] >= 1000, "High", "Low")

Nested IF: Category = IF([Amount] >= 1000, "High", IF([Amount] >= 500, "Medium", "Low"))

DIVIDE

Safe division (handles divide by zero): Margin = DIVIDE([Profit], [Revenue])

Better than: [Profit] / [Revenue]

DAX Best Practices

Use measures for aggregations: Not calculated columns!

Format measures: Set currency, percentage, decimal places

Name clearly: "Total Sales" not "Measure1"

Comment complex formulas: Use // for comments

Test incrementally: Build formula step by step

Try This Exercise

Create these in your model:

1. Calculated Column: FullYear = YEAR(Sales[OrderDate])

2. Measure: Total Revenue = SUM(Sales[Amount])

3. Measure with IF: High Value Count = COUNTROWS(FILTER(Sales, Sales[Amount] >= 1000))

4. Test in visual: Create card showing Total Revenue

Common Mistakes

Using = instead of ==: Use = for assignment, == for comparison inside formulas

Not using table names: Always use TableName[Column] for clarity

Calculated column for aggregation: Use measure instead!

Forgetting parentheses: AVERAGE(Sales[Amount]) not AVERAGE Sales[Amount]

DAX Intellisense

Power BI helps you write DAX:

Auto-complete: Start typing, suggestions appear

Function tooltip: Hover over function to see syntax

Error indicators: Red underline shows errors

Next Steps

Now let's learn about calculated columns in depth!

Tip: DAX looks scary but starts simple. Master basics before advanced patterns!