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

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: kpbray
• Source: kpbray/power-bi-agent-skills
• License: MIT

Install and usage instructions live in the source repository linked above.