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

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

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

Multi-line Function Formatting

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

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

Core DAX Patterns

Aggregation Measures

/// 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:

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

Percentage of Total

/// 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

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

Time Intelligence Patterns

Year-to-Date (YTD)

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

Month-to-Date (MTD)

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

Quarter-to-Date (QTD)

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

Prior Year (PY)

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

Year-over-Year Growth

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

Prior Year YTD

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

Moving Average

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

Filter Context Manipulation

CALCULATE Basics

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

Remove Filters with ALL

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

Remove Specific Filters

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

Keep Specific Filters with ALLEXCEPT

/// 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

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

AVERAGEX

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

RANKX

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

Top N Pattern

/// 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

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

ADDCOLUMNS

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

FILTER

/// 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

/// 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

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

COALESCE for Blank Handling

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

ISBLANK Check

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

TMDL Measure Format

Measures in TMDL files:

/// 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.