Power BI DAX Companion

An expert agent for writing and optimizing DAX (Data Analysis Expressions) formulas in Power BI, covering measure design, time intelligence calculations, iterator functions, and performance optimization following Microsoft's official DAX best practices.

When to Use This Agent

Choose DAX Companion when:

• Writing complex DAX measures for Power BI reports
• Implementing time intelligence calculations (YTD, MTD, YoY comparison)
• Optimizing slow DAX queries with Performance Analyzer
• Designing calculation groups for reusable measure patterns
• Debugging DAX formulas that return unexpected results

Consider alternatives when:

• Designing the underlying data model (use a Power BI Data Modeling agent)
• Building Power BI paginated reports with RDL (use SSRS tooling)
• Writing M/Power Query for data transformation (use a data engineering agent)

Quick Start

Copy
# .claude/agents/power-bi-dax-companion.yml
name: DAX Companion
model: claude-sonnet-4-20250514
tools:
  - Read
  - Write
  - Bash
  - Glob
  - Grep
prompt: |
  You are a Power BI DAX expert following Microsoft's official
  best practices. Write efficient, well-formatted DAX measures
  with clear variable names. Optimize for the VertiPaq engine.
  Always explain the filter context of your formulas.

Example invocation:

Copy
claude --agent power-bi-dax-companion "Write DAX measures for a
  sales dashboard: YTD revenue, same period last year, YoY growth
  percentage, and rolling 3-month average - all respecting slicer
  selections for product category and region"

Core Concepts

DAX Measure Patterns

Copy
// Year-to-Date with slicer awareness
YTD Revenue =
VAR _CurrentRevenue =
    CALCULATE(
        SUM(FactSales[Amount]),
        DATESYTD(DimDate[Date])
    )
RETURN
    _CurrentRevenue

// Same Period Last Year
SPLY Revenue =
CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR(DimDate[Date])
)

// Year-over-Year Growth
YoY Growth % =
VAR _Current = [Total Revenue]
VAR _PriorYear = [SPLY Revenue]
RETURN
    DIVIDE(_Current - _PriorYear, _PriorYear)

Filter Context Fundamentals

Function	Effect on Filter Context
CALCULATE	Modifies filter context
ALL	Removes filters from table/column
KEEPFILTERS	Intersects with existing filters
REMOVEFILTERS	Explicitly removes filters
USERELATIONSHIP	Activates inactive relationship
CROSSFILTER	Changes cross-filter direction
TREATAS	Applies virtual relationship

Performance Optimization

Anti-Pattern	Better Approach	Why
IF(HASONEVALUE(...))	SELECTEDVALUE(col, default)	Simpler, faster
Nested CALCULATE	Variables with single CALCULATE	Reduces engine passes
FILTER(table, ...)	KEEPFILTERS + column filter	Engine optimized
SUMX with IF	CALCULATE with filter	Avoids row-by-row
String concatenation in measures	Pre-computed in Power Query	VertiPaq not optimized for strings

Configuration

Parameter	Description	Default
date_table	Date dimension table name	DimDate
fiscal_year_start	Fiscal year start month	January (1)
currency_format	Default currency formatting	$#,##0.00
percentage_format	Default percentage formatting	0.0%
blank_handling	How to handle BLANK() results	Show as blank
calculation_groups	Use calculation groups for TI	When > 5 TI measures
variable_prefix	DAX variable naming prefix	_ (underscore)

Best Practices

1. Use variables to improve readability and performance. Variables (VAR ... RETURN) are evaluated once and reused, which prevents the engine from recalculating the same expression multiple times. They also make complex measures self-documenting. Name variables descriptively with an underscore prefix: VAR _PriorYearRevenue = .... A measure with five variables is easier to debug than five nested CALCULATE calls.

2. Understand the difference between filter context and row context. Filter context comes from slicers, visual filters, and CALCULATE. Row context comes from iterators like SUMX and FILTER. Confusing them is the most common source of DAX bugs. CALCULATE performs context transition—it converts row context into filter context. Know when this transition happens and whether you want it.

3. Avoid iterating over large tables when aggregation functions exist. SUMX(Sales, Sales[Quantity] * Sales[Price]) iterates row by row. If you can pre-compute the multiplication as a calculated column or in Power Query, SUM(Sales[LineTotal]) is significantly faster. Use iterator functions only when the calculation genuinely requires row-by-row logic that can't be expressed as simple aggregation.

4. Use DIVIDE instead of the division operator. DIVIDE(numerator, denominator, alternateResult) handles division by zero gracefully, returning BLANK() or a specified alternate value. The / operator returns an error on division by zero, which breaks visuals. DIVIDE is also slightly more efficient because the engine can optimize the zero check.

5. Test measures with DAX Studio or Performance Analyzer before deploying. Performance Analyzer in Power BI Desktop shows how long each visual's DAX query takes. DAX Studio lets you run queries directly and examine the storage engine plan. Identify measures that generate excessive storage engine queries (materializations) and restructure them to reduce engine load. A measure that runs in 5ms alone may cause 10-second report loads when used across 20 visuals.

Common Issues

Measure ignores slicer selections. This happens when ALL() or REMOVEFILTERS() removes the slicer's filter context. Check your CALCULATE modifiers carefully. If you need to ignore some filters while keeping others, use ALLEXCEPT() to specify which filters to preserve, or KEEPFILTERS() to intersect rather than replace. Test measures with specific slicer combinations to verify filter context behavior.

Time intelligence functions return blanks for some periods. Time intelligence requires a contiguous, complete date table marked as a date table in the model. Verify your date dimension has no gaps (every day from the earliest to latest date exists), is marked as a date table, and the date column has no duplicates. Missing dates cause DATESYTD, SAMEPERIODLASTYEAR, and other time intelligence functions to return unexpected blanks.

Complex measures are extremely slow. Break the measure into components and test each independently. The most common performance killers are: iterating over large tables (SUMX over millions of rows), bidirectional cross-filtering that multiplies the filter space, and nested CALCULATE with conflicting filter arguments. Refactor to use pre-aggregated measures in variables, and move string operations and complex calculations to Power Query preprocessing.