[Data Analysis & BI]Write DAX Measures with AI

Summary: Use AI to write DAX measures that work right out of the box—say goodbye to errors and head-scratching.

1. Pain Point

Have you ever had this experience: your boss says, "Compare this year's cumulative sales against the same period last year." You open Power BI, create a new measure (a calculation rule in Power BI that recomputes automatically as filters change—think of it as a formula that updates itself), type out CALCULATE, hit Enter—and get an error.

Try again. Another error. You search the web, read ten tutorials—eight of them are vague, and the other two still fail when you paste the code in. In the end, you either rope in a colleague to rescue you or grind through it yourself, and the whole afternoon is gone.

Honestly, DAX (Data Analysis Expressions, the formula language Power BI uses for calculations) isn't that hard on its own. The tricky part is its "filter context" personality: go with it and you get results; go against it and it gives you grief. The good news is that with Copilot (the AI assistant built into Power BI), you can have it draft the code first, then you tweak and verify. This article shows you exactly how—and for every measure, I'll give you a verified, working "reference answer."

2. Target Output

After reading this article, you'll have a set of truly runnable DAX measures you can paste straight into your own Power BI report:

  • Total Sales (using SUMX to compute Quantity × UnitPrice row by row, then sum)
  • Last-Year Sales (using CALCULATE with a time intelligence function to compare against the same period last year)
  • Big-Order Sales (only orders whose quantity exceeds a threshold)
  • Year-to-Date / Month-to-Date Sales (using TOTALYTD / TOTALMTD)
  • Year-over-Year Growth Rate (using DIVIDE for safe division, to avoid divide-by-zero errors)

You'll also learn how to write a precise prompt for Copilot so the DAX it returns is close to what you need, plus a few pitfalls to help you avoid common traps.

3. Case Practice

The data model used in this article (all the DAX below depends on it—check your field names before copying)

  • Sales (sales fact table—the big table that records each business transaction): Sales[OrderDate] order date, Sales[ProductID] product ID, Sales[Quantity] quantity, Sales[UnitPrice] unit price
  • Date (date dimension table—a dedicated helper table for dates): Date[Date] date, Date[Year] year, Date[Month] month
  • Relationship (the "wire" that links two tables together): Sales[OrderDate] → Date[Date], many-to-one, kept active (i.e., the line is solid and in effect)

Step 1: State the Requirement Clearly (The Prompt for Copilot)

In Power BI's Copilot pane in Report view (or the DAX query view), write a prompt like the one below. The key is: be specific about table names, field names, the functions to use, and the outcome you want. The more specific you are, the more accurate Copilot's output.

Prerequisite: Having Copilot write DAX for you requires paid capacity (F2+/P1+ and the like) + a Pro or PPU license, with the admin toggle switched on (same criteria as B21; go by the current Microsoft Learn docs)—the button isn't available in the free version of Power BI Desktop. If you're using the free Desktop, or don't have Copilot yet, that's completely fine—just use the "reference answers" below to create your measures manually (same approach as in B21 Your First Report).

Sample prompt (copy as-is, then swap in your own field names) "Based on the Sales table and Date table, write a measure called [总销售额]. Use SUMX to iterate over each row of the Sales table, multiply Sales[Quantity] by Sales[UnitPrice], then sum the results. Use the field names from my model."

(Measure names stay in Chinese — [总销售额] means "Total Sales" — so the prompts match the model and code below. Rename freely in your own model.)

Copilot will usually respond with a chunk of DAX. Don't blindly copy it—read it through, then verify. Starting in the next section, I'll give you a "reference answer" set that I've already validated and that runs. Use it as a sanity check—if what the AI returns matches this logic, you're good to go.

Step 2: Follow the Reference Answers—Build Each Measure One by One

Where to do it: in Report view, click "Modeling" → "New measure" (or right-click a table name → New measure), then paste the code below in full. Be sure to swap the field names for your own model's field names.

① Total Sales (SUMX row-by-row sum)

dax
总销售额 =
SUMX (
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

SUMX works like this: it scans the Sales table one row at a time, computes "Quantity × UnitPrice" for each row, then sums all the results. It's more flexible than a plain SUM(amount_column)—even if your table doesn't have a pre-built amount column, SUMX can compute one on the fly.

② Last-Year Sales (CALCULATE with modified filter + time intelligence)

dax
去年销售额 =
CALCULATE (
    [总销售额],
    SAMEPERIODLASTYEAR ( Date[Date] )
)

CALCULATE is the "transformer" of DAX: it can temporarily change filter conditions, then recompute. Here, it shifts the date filter to "the same date range one year ago," so [总销售额] is recomputed only within that prior-year window. SAMEPERIODLASTYEAR is a time intelligence function (specialized for year-over-year, period-over-period, and running-total calculations tied to dates)—it shifts the current date back by one full year.

③ Big-Order Sales (CALCULATE + FILTER to pick specific rows)

dax
大单销售额 =
CALCULATE (
    [总销售额],
    FILTER ( Sales, Sales[Quantity] > 10 )
)

FILTER picks out the rows from Sales where "Quantity > 10" and hands them to CALCULATE as the new filter scope. So [总销售额] only sums up the "big orders." Just replace 10 with your business threshold.

④ Year-to-Date Sales (TOTALYTD)

dax
年初至今销售额 =
TOTALYTD ( [总销售额], Date[Date] )

TOTALYTD (Total Year To Date) computes the running total "from January 1 of this year up to the current filter date." The first argument is the measure to accumulate; the second is the date column from the date table.

⑤ Month-to-Date Sales (TOTALMTD)

dax
月初至今销售额 =
TOTALMTD ( [总销售额], Date[Date] )

TOTALMTD (Total Month To Date) works the same way: the running total "from the 1st of the current month up to the current date."

⑥ Year-over-Year Growth Rate (DIVIDE for safe division)

dax
同比增长率 =
DIVIDE (
    [总销售额] - [去年销售额],
    [去年销售额]
)

The difference between DIVIDE and a plain / is that when the denominator is 0 (e.g., no data last year), DIVIDE doesn't error out—it returns BLANK. For any financial or operational ratio, always prefer DIVIDE.

Build all six measures, drop them onto a report, slice by year and month, and the numbers will update automatically—that's the power of measures: the same formula, recompute automatically under any filter context.

4. Principle Recap

The core of DAX is "context." Iterator functions like SUMX compute row by row via row context; CALCULATE is clever because it can "promote" row context into filter context, thereby changing the scope of the calculation. Time intelligence functions (SAMEPERIODLASTYEAR, TOTALYTD, TOTALMTD) can automatically align to last year or to year-to-date because, behind the scenes, a continuous, properly-marked Date dimension table is holding it all together. Once you understand "filter context + the date table," when you read DAX written by AI, you'll know exactly what each step is changing and why it produces the number it does—instead of mindlessly copy-pasting.

5. Pitfall Guide

  1. Use "Mark as date table" on your date table. Time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR, etc.) have one true prerequisite: the date column must be continuous and gap-free, and the Sales[OrderDate] → Date[Date] relationship must be active. On top of that, marking the Date table as the official date table helps Power BI recognize the time hierarchy correctly and avoid ambiguity. Path: Report view → select the Date table → "Table tools" → Mark as date table.

  2. The relationship must be active and the direction must be correct. The Sales[OrderDate] → Date[Date] relationship must be active (a solid line, not a dashed one). If it's inactive, time intelligence functions can't read through it, and you'd need USERELATIONSHIP to enable it on the fly. Beginners: make sure it's active first, or you'll spend ages troubleshooting.

  3. Don't use FILTER on a large fact table for simple conditions. FILTER(Sales, ...) scans the entire sales table row by row—painfully slow on large data. (Performance note: push the predicate into CALCULATE's filter arguments; the engine can perform "predicate pushdown" and only scan the rows it needs—much faster than FILTER expanding the entire table first.) For simple conditions like "Quantity > 10," prefer CALCULATE's shorthand: CALCULATE([总销售额], Sales[Quantity] > 10); only reach for FILTER when you need "row-vs-row" comparisons (e.g., versus the table-wide average).

  4. Don't mix up measures and calculated columns. Measures don't take up storage and recompute in real time as filters change—perfect for aggregates, ratios, and YoY comparisons. Calculated columns are computed row by row when the model is built and stored as fields in the table—perfect for use as filter or group-by keys. For analytical metrics, prefer measures; don't build calculated columns reflexively, or your file will balloon and refreshes will crawl.

  5. Fiscal year ≠ calendar year? Use year_end_date. TOTALYTD defaults to a December 31 cutoff. If your fiscal year ends in June, write TOTALYTD([总销售额], Date[Date], "6/30")—otherwise the cumulative window will be off by a full period.

6. Advanced Extensions

If you want to deepen your "use AI to write DAX" skills, first revisit B21 Your First Report at the L1 level—it's the foundation for this article and covers the most basic things: "your first report + your first SUM measure + Copilot's limits." The data modeling, date dimension table, and table relationships used in this article fall outside B21's scope; the standard steps to build a date dimension table are fully demonstrated in B23 Data Models & Power Query ETL with AI — follow that walkthrough and combine it with this article's DAX time intelligence.

Going further, at the L3 level we'll cover the hard-core stuff: dynamic ranking with RANKX, share-of-total with CALCULATE + ALLEXCEPT, and letting Copilot help you write variables (VAR) to make complex measures more readable. By then, you'll be a true DAX expert.