Power BI: optimize DAX with AI and test the results

Key insights

• The example measure calculates sales for purchases within 30 days of the same customer's previous purchase day.
• The original measure uses nested iterations over customers, visible dates, and earlier purchase dates.
• SQLBI asked AI to verify candidate measures against a frozen baseline before benchmarking them.
• The performance tests varied query groupings and result sizes, and included cold- and warm-cache timings.
• A human rewrite reduced the date range scanned, but that change could affect performance depending on the query.

Power BI DAX optimization with AI can produce alternative measures and help test whether they return the same results and run faster. This approach matters to Power BI modelers who need to improve measures without changing their business meaning. SQLBI demonstrates the workflow using a repeat-purchase sales measure and a Contoso model.

What it does

DAX optimization is not simply asking an AI system to rewrite a formula. The replacement must preserve the measure's contract: the business definition, how filters and totals behave, and the expected treatment of blanks. It also needs to perform well across the kinds of queries people will run against the model.

In the example, the measure returns sales for a purchase day when that day is within 30 calendar days of the same customer's immediately preceding purchase day. The starting measure works, but it iterates over customers, visible purchase dates, and earlier transactions to locate the previous date. SQLBI points out that these nested iterations can make the calculation slow, particularly as the measure processes many customers and dates.

The demonstrated method treats AI as an assistant for generating and testing alternatives, not as an authority whose answer is automatically accepted. The aim is to get a candidate measure, compare its results against a frozen copy of the original, and then benchmark it on multiple query shapes. Readers exploring related Power BI topics can apply the same distinction: a correct rewrite is not necessarily a faster one.

Who it is for and what you need

This workflow is aimed at people who can inspect and evaluate DAX measures and understand the semantic model they are changing. The example uses Power BI Desktop, a Contoso 1M model, and an AI system connected to the Power BI model using MCP. The transcript does not identify a specific AI product or give setup instructions for that connection.

You need a working measure that serves as the baseline, a clear definition of its expected behavior, and a way to execute queries and compare timings. The example's prompt also asks for separate candidate measures, correctness tests, performance tests, and a report of the results. The transcript does not state licence requirements, product availability, or a rollout stage for this workflow.

SQLBI emphasizes that DAX performance depends on more than the text of a formula. The model, the query being run, and the engine's handling of the calculation all matter. That makes familiarity with the model and a willingness to review test results important; the demonstration does not present AI output as a substitute for that judgment.

How it works

The process starts by defining the measure's contract before requesting an optimization. In the example, that includes identifying customers by CustomerKey and specifying the 30-day repeat-purchase rule. SQLBI says the full contract includes further details about dates, filters, totals, blanks, and acceptance criteria, though those details are not reproduced in the transcript.

1. Keep the existing measure as a frozen baseline and ask AI to create a separate candidate measure, rather than replacing the working measure.
2. Ask for correctness queries with different grouping structures and scenarios. The demonstrated tests compare the baseline and candidate results, including checks grouped by customer.
3. Only after checking correctness, run performance queries with varied structures and result sizes. The example includes a grand total, customers, date and category, and month, store, and country groupings.
4. Review the report, including timings for the original and candidate measures, cold- and warm-cache results, and the candidate's recorded changes. Retain a rewrite only if it meets the correctness requirement and performs acceptably for relevant queries.

The AI-generated candidates used different approaches. One gathered date and customer combinations before applying a filter; another used the DAX window function OFFSET to find a previous date. A third used different logic depending on whether the current context contained one customer. The example report compared these candidates rather than treating any one rewrite as automatically best.

SQLBI also tested a human-written alternative. That version limited the dates considered to the period beginning 30 days before the current date, with the intention of reducing the temporary data processed, and used a window function to find a preceding purchase day. The transcript explains a tradeoff: narrowing the date range can make the calculation depend on the cell being evaluated, whereas gathering dates without that condition can create a table that does not change between evaluations.

Limitations and open questions

Results from this demonstration do not establish that AI will optimize every DAX measure or outperform an experienced modeler. The test uses one measure and one model, and performance depends on the query shape. A rewrite that helps one scenario may not be the best choice in another, so a single benchmark query is not enough to support a broad performance claim.

The transcript is cut off while describing the comparison of the human and AI-optimized measures. It does not provide the final timings or a complete verdict about which version won. It also does not include the full contract, the exact test results, AI service details, or licensing and availability information. Treat the example as a method for building a stronger evaluation process, not as evidence that a particular candidate will be faster in your model.

The practical takeaway is to preserve the original, require result checks before timing, and evaluate multiple realistic query shapes. AI can help generate candidate logic and test queries, while the modeler remains responsible for deciding whether the contract is correctly represented and whether measured results are useful for the workload.

Sources

• SQLBI demonstration of AI-assisted DAX optimization
• SQLBI website

Frequently asked questions

Can AI optimize a DAX measure in Power BI?

It can propose alternative DAX and help create correctness and performance tests. The example stresses validating the result and measuring performance rather than accepting a rewrite on its own.

How do you check whether an optimized DAX measure is correct?

Compare it with a frozen baseline across multiple scenarios and grouping structures. In the demonstration, correctness checks run before performance benchmarking.

Does an optimized DAX measure always run faster?

No such guarantee is established. The demonstration tests different query shapes and notes that a rewrite's performance can depend on the query being evaluated.

What licence is needed for AI-assisted DAX optimization?

The material does not state a licence requirement or identify the AI product used. It describes connecting an AI system to a Power BI model using MCP.