Tag: AI DAX Writing

Copilot DAX generation is one of the most anticipated features in Power BI — and one of the most misunderstood. Some analysts expect Copilot to write production-ready DAX on the first attempt. Others dismiss it entirely after a few bad results. The reality falls between these extremes: Copilot is a capable DAX assistant that excels at certain patterns and consistently struggles with others. Knowing which is which transforms Copilot from a frustration into a genuine productivity tool.

This assessment is based on real-world usage patterns across common business intelligence scenarios. It covers what Copilot gets right, where it fails, and specific techniques to improve its output.

What Copilot DAX Generation Does Well

Simple Aggregations

Copilot handles basic aggregation measures reliably. Asking “Create a measure for total sales” or “Calculate the average order value” produces correct, clean DAX in nearly all cases. SUM, AVERAGE, COUNT, DISTINCTCOUNT, MIN, and MAX over a single column work consistently.

These are the lowest-complexity DAX patterns, but they represent a significant portion of the measures most organizations need. For teams building out a new data model, Copilot can scaffold dozens of basic measures in minutes rather than hours.

Basic Time Intelligence

Standard time intelligence functions work well when the model has a properly marked date table. Copilot reliably generates year-over-year comparisons using SAMEPERIODLASTYEAR, period-to-date calculations using DATESYTD/DATESMTD, and rolling averages using DATESINPERIOD.

The key requirement is having a date table that Power BI recognizes as such. Without it, time intelligence requests produce incorrect or error-throwing DAX.

CALCULATE with Straightforward Filters

Copilot generates clean CALCULATE expressions when the filter logic is straightforward: filtering by a single column value, filtering by a date range, or combining two or three simple conditions. For example, asking “Total sales for the Western region in Q4” produces correct CALCULATE with appropriate filter arguments.

DIVIDE for Safe Division

Copilot consistently uses DIVIDE instead of the division operator when generating ratio measures. This is good practice — DIVIDE handles division by zero gracefully. Even when asked simply for a “conversion rate,” Copilot wraps the calculation in DIVIDE with an appropriate alternate result.

Where Copilot DAX Generation Struggles

Complex Iterator Functions

SUMX, AVERAGEX, and other iterator functions work correctly over a single table with a simple expression. But when the row expression involves lookups to other tables, conditional logic, or nested calculations, Copilot frequently generates DAX that either errors out or produces incorrect results.

A request like “Calculate the weighted average price where the weight is the quantity sold, grouped by product category” requires a SUMX with a RELATED lookup and a DIVIDE — Copilot often gets the structure right but misidentifies which table to iterate over or which relationship to traverse.

Advanced Time Intelligence

Beyond basic year-over-year and period-to-date, Copilot struggles with fiscal calendars that don’t align with the standard calendar, custom time intelligence involving irregular periods, parallel period calculations with complex offsets, and semi-additive measures like inventory snapshots that require LASTDATE or LASTNONBLANK.

If your organization uses a 4-4-5 retail calendar or a fiscal year starting in April, do not expect Copilot to generate correct time intelligence on the first attempt. You will need to provide explicit context about your calendar structure in the prompt.

Many-to-Many Relationships

Models with many-to-many relationships through bridge tables consistently confuse Copilot. The generated DAX often ignores the bridge table entirely, applies incorrect cross-filter directions, or generates CALCULATE expressions with filters that do not propagate correctly across the many-to-many path.

Dynamic Security and Context Manipulation

Copilot does not generate reliable DAX for dynamic security scenarios (USERNAME, USERPRINCIPALNAME in filter expressions), CROSSFILTER modifications, USERELATIONSHIP to activate inactive relationships, or complex filter context transitions using ALLEXCEPT, REMOVEFILTERS, or KEEPFILTERS.

These are advanced patterns that even experienced DAX developers approach carefully. Copilot should not be expected to handle them.

Multi-Level Measure References

When a measure references another measure, which references another measure, Copilot sometimes loses track of the dependency chain. A request to “modify the YTD Revenue measure to use the Net Revenue measure instead of Gross Revenue” may produce DAX that recalculates from scratch rather than swapping the reference, especially if the intermediate measures are not well-described.

How to Write Better Prompts for DAX Generation

The quality of Copilot’s DAX output is directly correlated with prompt specificity. Vague prompts produce vague DAX.

Instead of: “Create a revenue measure”

Write: “Create a measure called Total Net Revenue that sums the Net Amount column from the Sales table, filtered to rows where Order Status equals Completed”

Instead of: “Show me the trend”

Write: “Create a measure that calculates the month-over-month percentage change in Total Net Revenue using DATEADD to offset by one month”

Key prompting techniques:

• Name the tables and columns explicitly. Do not assume Copilot knows which “amount” or “date” you mean
• Specify the aggregation type. “Sum of” is different from “average of” is different from “count of”
• Mention the filter context. If the measure should only apply to certain rows, state the filter conditions
• Reference existing measures by name. If the new measure should build on an existing one, name it explicitly
• State the expected output format. “Return as a percentage” or “format as currency” helps Copilot add FORMAT or appropriate DIVIDE logic

The Review-Before-Deploy Workflow

Every piece of Copilot-generated DAX should go through a review before being deployed to production reports. This is not a criticism of Copilot — it is standard practice for any AI-generated code.

The four-step review:

1. Read the DAX: Does the logic match what you requested? Are the table and column references correct?
2. Check the result: Create a simple visual using the new measure. Does the number match your expectation? Cross-reference against a known-correct calculation
3. Test edge cases: What happens when the filter context is empty? When a dimension value has no matching fact rows? When the date range is outside your data?
4. Evaluate performance: Use DAX Studio or the Performance Analyzer to check the query plan. Copilot sometimes generates correct but inefficient patterns — nested CALCULATE where a single CALCULATE with multiple filters would suffice, or SUMX where CALCULATE with SUM would work

Using Copilot as a DAX Learning Tool

For analysts learning DAX, Copilot serves as an effective tutor. Ask it to generate a measure, study the pattern, then modify it. This is often faster than reading documentation because the generated DAX is specific to your model.

Effective learning prompts:

• “Write a running total measure and explain each function” — Copilot generates the DAX and can explain what each line does
• “What does this measure do?” (followed by pasting existing DAX) — Copilot translates complex DAX into plain language
• “Rewrite this measure to be more efficient” — Copilot sometimes identifies optimization opportunities in existing DAX

This learning use case is where Copilot provides the most consistent value. Even when its generated DAX needs correction, the pattern and structure it produces are educational.

Performance Implications of Copilot-Generated DAX

Copilot tends to generate DAX that prioritizes correctness over performance. This means it sometimes produces patterns that work but are not optimal for large datasets.

Common performance patterns to watch for:

• Unnecessary iterators: SUMX over a table when CALCULATE + SUM would produce the same result without row-by-row evaluation
• Redundant CALCULATE wrapping: Wrapping simple expressions in CALCULATE when no filter modification is needed
• Missing variables: Repeating the same sub-expression multiple times instead of storing it in a VAR
• Over-specified filters: Adding filter conditions that are already implicit in the model’s relationships

For models under 10 million rows, these inefficiencies are rarely noticeable. For larger models, review Copilot-generated DAX with DAX Studio before deploying to ensure query performance meets your requirements.

Frequently Asked Questions