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:

•Test measures - Verify calculations in a visual
•Validate - Use the best-practices skill to check DAX quality
•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.