dax

---
name: dax
description: "Writes DAX measures, calculated columns, and calculations for Power BI. Use for business logic, time intelligence, and analytical calculations."
---

# DAX Skill

This skill helps write DAX measures, calculated columns, and calculations for Power BI semantic models.

## When to Use This Skill

- Creating measures for business calculations
- Implementing time intelligence (YTD, MTD, PY comparisons)
- Writing filter context manipulations
- Building KPIs and scorecards
- Creating ranking and top N analyses
- Calculating percentages, ratios, and growth rates

## DAX Formatting Standards

Follow SQLBI formatting conventions for readable, maintainable DAX:

### Basic Structure

```dax
Measure Name =
VAR VariableName = Expression
VAR AnotherVariable = AnotherExpression
RETURN
    Result
```

### Indentation Rules

- Use 4 spaces for indentation (or consistent tabs)
- Each function argument on a new line for complex expressions
- Align operators vertically

### Good Formatting Example

```dax
Total Sales =
VAR SalesAmount = SUM(Sales[Amount])
VAR ReturnAmount = SUM(Returns[Amount])
VAR NetSales = SalesAmount - ReturnAmount
RETURN
    NetSales
```

### Multi-line Function Formatting

```dax
Sales YTD =
CALCULATE(
    [Total Sales],
    DATESYTD(Date[Date])
)
```

```dax
Filtered Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL(Products),
        Products[Category] = "Electronics"
    )
)
```

## Core DAX Patterns

### Aggregation Measures

```dax
/// Total of all sales amounts
Total Sales =
SUM(Sales[Sales Amount])

/// Count of distinct customers
Customer Count =
DISTINCTCOUNT(Sales[Customer ID])

/// Average order value
Average Order Value =
AVERAGE(Sales[Order Amount])

/// Maximum sale amount
Max Sale =
MAX(Sales[Sales Amount])
```

### Safe Division

Always use DIVIDE() instead of the `/` operator:

```dax
/// Profit margin percentage
Profit Margin % =
VAR Revenue = SUM(Sales[Revenue])
VAR Profit = SUM(Sales[Profit])
RETURN
    DIVIDE(Profit, Revenue, 0)
```

### Percentage of Total

```dax
/// Sales as percentage of total
Sales % of Total =
VAR CurrentSales = [Total Sales]
VAR AllSales = CALCULATE([Total Sales], ALL(Sales))
RETURN
    DIVIDE(CurrentSales, AllSales, 0)
```

### Cumulative Total

```dax
/// Running total of sales
Cumulative Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        ALL(Date),
        Date[Date] <= MAX(Date[Date])
    )
)
```

## Time Intelligence Patterns

### Year-to-Date (YTD)

```dax
/// Year-to-date sales
Sales YTD =
CALCULATE(
    [Total Sales],
    DATESYTD(Date[Date])
)
```

### Month-to-Date (MTD)

```dax
/// Month-to-date sales
Sales MTD =
CALCULATE(
    [Total Sales],
    DATESMTD(Date[Date])
)
```

### Quarter-to-Date (QTD)

```dax
/// Quarter-to-date sales
Sales QTD =
CALCULATE(
    [Total Sales],
    DATESQTD(Date[Date])
)
```

### Prior Year (PY)

```dax
/// Sales from same period last year
Sales PY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(Date[Date])
)
```

### Year-over-Year Growth

```dax
/// Year-over-year growth percentage
Sales YoY % =
VAR CurrentSales = [Total Sales]
VAR PriorYearSales = [Sales PY]
RETURN
    DIVIDE(
        CurrentSales - PriorYearSales,
        PriorYearSales,
        BLANK()
    )
```

### Prior Year YTD

```dax
/// Year-to-date sales from last year
Sales PYTD =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(Date[Date]),
    DATESYTD(Date[Date])
)
```

### Moving Average

```dax
/// 3-month moving average
Sales 3M Avg =
AVERAGEX(
    DATESINPERIOD(
        Date[Date],
        MAX(Date[Date]),
        -3,
        MONTH
    ),
    [Total Sales]
)
```

## Filter Context Manipulation

### CALCULATE Basics

```dax
/// Sales for Electronics category only
Electronics Sales =
CALCULATE(
    [Total Sales],
    Products[Category] = "Electronics"
)
```

### Remove Filters with ALL

```dax
/// Sales ignoring all filters
Total Sales All =
CALCULATE(
    [Total Sales],
    ALL(Sales)
)
```

### Remove Specific Filters

```dax
/// Sales ignoring product filter only
Sales All Products =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(Products)
)
```

### Keep Specific Filters with ALLEXCEPT

```dax
/// Percentage within category
% of Category =
VAR CurrentSales = [Total Sales]
VAR CategorySales =
    CALCULATE(
        [Total Sales],
        ALLEXCEPT(Products, Products[Category])
    )
RETURN
    DIVIDE(CurrentSales, CategorySales, 0)
```

## Iterator Functions

### SUMX

```dax
/// Extended price (quantity * unit price)
Extended Price =
SUMX(
    Sales,
    Sales[Quantity] * Sales[Unit Price]
)
```

### AVERAGEX

```dax
/// Average sales per customer
Avg Sales per Customer =
AVERAGEX(
    VALUES(Customers[Customer ID]),
    [Total Sales]
)
```

### RANKX

```dax
/// Product rank by sales
Product Rank =
RANKX(
    ALL(Products[Product Name]),
    [Total Sales],
    ,
    DESC,
    Dense
)
```

### Top N Pattern

```dax
/// Sales from top 10 products only
Top 10 Products Sales =
CALCULATE(
    [Total Sales],
    TOPN(
        10,
        ALL(Products[Product Name]),
        [Total Sales],
        DESC
    )
)
```

## Table Functions

### SUMMARIZE

```dax
/// Sales summary by category
Category Summary =
SUMMARIZE(
    Sales,
    Products[Category],
    "Total Sales", [Total Sales],
    "Avg Price", AVERAGE(Sales[Unit Price])
)
```

### ADDCOLUMNS

```dax
/// Products with calculated fields
Products Extended =
ADDCOLUMNS(
    Products,
    "Sales Amount", [Total Sales],
    "Rank", [Product Rank]
)
```

### FILTER

```dax
/// High-value orders only
High Value Orders =
CALCULATE(
    [Order Count],
    FILTER(
        Sales,
        Sales[Amount] > 1000
    )
)
```

## Variables (VAR/RETURN)

Always use variables for:
- Reused expressions (calculate once)
- Complex logic (readability)
- Intermediate calculations

```dax
/// Complex calculation with variables
Profit Analysis =
VAR TotalRevenue = SUM(Sales[Revenue])
VAR TotalCost = SUM(Sales[Cost])
VAR TotalProfit = TotalRevenue - TotalCost
VAR ProfitMargin = DIVIDE(TotalProfit, TotalRevenue, 0)
VAR MarginCategory =
    SWITCH(
        TRUE(),
        ProfitMargin >= 0.3, "High",
        ProfitMargin >= 0.1, "Medium",
        "Low"
    )
RETURN
    MarginCategory
```

## Error Handling

### IFERROR

```dax
/// Safe calculation with fallback
Safe Ratio =
IFERROR(
    [Total Sales] / [Total Cost],
    0
)
```

### COALESCE for Blank Handling

```dax
/// Replace blank with zero
Sales or Zero =
COALESCE([Total Sales], 0)
```

### ISBLANK Check

```dax
/// Conditional formatting flag
Has Sales =
NOT(ISBLANK([Total Sales]))
```

## TMDL Measure Format

Measures in TMDL files:

```tmdl
/// Year-to-date sales calculation
/// Use with Date table marked as date table
measure 'Sales YTD' =
	CALCULATE(
	    [Total Sales],
	    DATESYTD(Date[Date])
	)
	formatString: "$#,##0.00"
	displayFolder: Time Intelligence
	lineageTag: a1b2c3d4-e5f6-7890-abcd-ef1234567890
```

## Boundaries and Constraints

### DO
- Always use DIVIDE() instead of `/` operator
- Always use VAR for reused expressions
- Add descriptions using `///` comments
- Use meaningful variable names
- Format complex expressions across multiple lines
- Group related measures in displayFolders
- Test measures with different filter contexts

### DO NOT
- Never use implicit measures (drag-and-drop aggregations)
- Never nest CALCULATE more than 2-3 levels deep
- Avoid IFERROR around aggregations (masks data issues)
- Never reference measure results in calculated columns
- Avoid circular references between measures
- Never hardcode filter values when parameters work

## Workflow Integration

After creating measures:
1. **Test measures** - Verify calculations in a visual
2. **Validate** - Use the `best-practices` skill to check DAX quality
3. **Add to reports** - Use the `report-visuals` skill to display

## Common Issues

### "Circular dependency"
Measures reference each other in a loop. Break the cycle by restructuring.

### "Column not found"
Check table and column names match exactly (case-sensitive).

### "Cannot convert value"
Data type mismatch. Ensure compatible types in comparisons.

### "The value for column cannot be determined"
Filter context removed something needed. Review CALCULATE modifiers.

More agent context in kpbray/power-bi-agent-skills

9 other files this repository gives its agents.