dax

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.