Reviewing AI-written DAX and SQL

AI-written SQL and DAX usually parses, runs, and returns a plausible number. The mistakes that matter are semantic: the code answers a slightly different question from the one you asked. Syntax review won’t find them. You have to review for meaning, and then test against data where you already know the answer.

This is the checklist I’d work through before an AI-drafted query or measure reaches a report. The examples use a synthetic outpatient setup (referrals, appointments, diagnoses) and SQL that runs on SQL Server, PostgreSQL, and SQLite unless a note says otherwise.

The checklist

Copy this into a pull request template or a review prompt. Each item has a section below.

Before reading the code
[ ] I can state the intended output grain in one sentence
[ ] I have worked out the right answer for a small case by hand

SQL
[ ] No join multiplies rows (the key on the "one" side is unique)
[ ] NULLs handled on purpose: no NOT IN over nullable columns, no silent drops by = or <>
[ ] LEFT JOIN conditions on the right-hand table sit in ON, not WHERE
[ ] Date ranges are half-open (>= start AND < end)
[ ] Stored time zone and reporting time zone are known and converted
[ ] No integer division
[ ] No DISTINCT hiding duplicates

DAX
[ ] Each CALCULATE filter replaces or intersects existing filters as intended
[ ] ALL / REMOVEFILTERS clear exactly the intended tables or columns
[ ] BLANK is handled on purpose (= treats BLANK as 0; + 0 brings back empty rows)
[ ] DIVIDE wherever the denominator can be zero or BLANK
[ ] Every relationship the measure relies on exists, is active or activated, and joins matching types
[ ] No FILTER over a whole table where a column predicate would do

Testing
[ ] Ran against a fixture with known answers, and the results match

Before reading the code

Write down the grain of the result (“one row per specialty per month”) and the answer for a tiny case you can work out by hand. Do this before you read the model’s code, because once you’ve read fluent code it’s hard not to accept its framing. If the model listed its assumptions, compare them with yours now. The AI playbook has prompts that ask for assumptions up front.

SQL

Join fan-out

A join to a table with several rows per key multiplies the rows on the other side. The count still looks reasonable, just bigger.

-- Wrong: a referral with two E11 codes counts each of its appointments twice
SELECT COUNT(*) AS appointments
FROM appointments a
JOIN diagnoses d ON d.referral_id = a.referral_id
WHERE d.code LIKE 'E11%';

-- Right: EXISTS tests membership without multiplying rows
SELECT COUNT(*) AS appointments
FROM appointments a
WHERE EXISTS (
    SELECT 1 FROM diagnoses d
    WHERE d.referral_id = a.referral_id AND d.code LIKE 'E11%'
);

For every join, ask what the key is on each side and whether it’s unique. Check the row count before and after each join, and test uniqueness directly with GROUP BY key HAVING COUNT(*) > 1.

NULL handling

NOT IN against a column containing a NULL returns no rows at all, because x NOT IN (..., NULL) can never be true. One walk-in appointment with no referral ID is enough to empty this list:

-- Wrong: returns nothing if any appointment has a NULL referral_id
SELECT referral_id FROM referrals
WHERE referral_id NOT IN (SELECT referral_id FROM appointments);

-- Right
SELECT r.referral_id FROM referrals r
WHERE NOT EXISTS (
    SELECT 1 FROM appointments a WHERE a.referral_id = r.referral_id
);

The quieter version: WHERE status <> 'Cancelled' also drops every row where status is NULL, such as appointments not yet outcomed. That might be what you want. Make it explicit either way (status IS NULL OR status <> 'Cancelled'), and remember that COUNT(status) skips NULLs while COUNT(*) doesn’t.

Filters in WHERE versus ON

A condition on the right-hand table of a LEFT JOIN placed in WHERE removes the rows the left join was there to keep.

-- Wrong: specialties with no attended appointments disappear
SELECT r.specialty, COUNT(a.appt_id) AS attended
FROM referrals r
LEFT JOIN appointments a ON a.referral_id = r.referral_id
WHERE a.status = 'Attended'
GROUP BY r.specialty;

-- Right: the condition belongs to the join
SELECT r.specialty, COUNT(a.appt_id) AS attended
FROM referrals r
LEFT JOIN appointments a
  ON a.referral_id = r.referral_id AND a.status = 'Attended'
GROUP BY r.specialty;

A missing row is harder to spot than a wrong number, because nobody sees a zero that isn’t there.

Date boundaries

BETWEEN '2025-03-01' AND '2025-03-31' on a datetime column stops at midnight at the start of 31 March, so the last day’s activity after 00:00 is lost.

-- Wrong on datetime columns
WHERE appt_start BETWEEN '2025-03-01' AND '2025-03-31'

-- Right: half-open range
WHERE appt_start >= '2025-03-01' AND appt_start < '2025-04-01'

Also check which calendar the model assumed. UK financial years start on 1 April, and “week” can mean ISO weeks, Sunday-start weeks, or a local convention.

Time zones

Ask first what the column holds. Many source systems store local time; others store UTC. If it’s UTC and you report UK days, casting straight to a date puts late-evening summer activity on the wrong day. An arrival at 23:30 UTC on 31 March 2025 was 00:30 on 1 April in UK time: a different day, month, and financial year.

-- Wrong if arrival_utc is stored in UTC
SELECT CAST(arrival_utc AS date) AS arrival_date, COUNT(*) AS attendances
FROM ed_attendances
GROUP BY CAST(arrival_utc AS date);

-- Right (SQL Server 2016+): mark as UTC, convert to UK time, then take the date
SELECT CAST(arrival_utc AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time' AS date) AS arrival_date,
       COUNT(*) AS attendances
FROM ed_attendances
GROUP BY CAST(arrival_utc AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time' AS date);

GMT Standard Time is the Windows name for UK time, including British Summer Time. In PostgreSQL the zone is Europe/London.

Integer division

In SQL Server, PostgreSQL, and SQLite, an integer divided by an integer is an integer, with the fraction truncated. A DNA rate of one in three becomes 0.

-- Wrong: returns 0
SELECT SUM(CASE WHEN status = 'DNA' THEN 1 ELSE 0 END)
     / SUM(CASE WHEN status IN ('Attended', 'DNA') THEN 1 ELSE 0 END) AS dna_rate
FROM appointments;

-- Right: force decimal arithmetic and guard against a zero denominator
SELECT 1.0 * SUM(CASE WHEN status = 'DNA' THEN 1 ELSE 0 END)
     / NULLIF(SUM(CASE WHEN status IN ('Attended', 'DNA') THEN 1 ELSE 0 END), 0) AS dna_rate
FROM appointments;

DISTINCT hiding duplicates

When a draft returns duplicate rows, models often add DISTINCT. That removes the symptom and keeps the cause. Here a patient who moved has two address rows:

-- Wrong: referral 1 still appears twice, once per address
SELECT DISTINCT r.referral_id, p.district
FROM referrals r
JOIN patient_address p ON p.patient_id = r.patient_id;

-- Right: pick the address that was valid on the referral date
SELECT r.referral_id, p.district
FROM referrals r
JOIN patient_address p
  ON p.patient_id = r.patient_id
 AND p.valid_from <= r.referral_date
 AND r.referral_date < p.valid_to;

Whenever you see DISTINCT, ask which duplicates it removes and why they exist. If it can’t be answered, the join is wrong.

DAX

The DAX examples assume a Referrals fact table, Specialty and 'Date' dimension tables, and a base measure [Referrals] = COUNTROWS ( Referrals ).

Filter context assumptions

A Boolean filter in CALCULATE replaces any existing filter on that column. It doesn’t intersect with it. A measure drafted for a card behaves differently in a matrix.

-- Wrong for a matrix with Priority on rows: every row shows the urgent count
Urgent Referrals =
CALCULATE ( [Referrals], Referrals[Priority] = "Urgent" )

-- Right if the measure should respect an existing Priority filter
Urgent Referrals =
CALCULATE ( [Referrals], KEEPFILTERS ( Referrals[Priority] = "Urgent" ) )

With KEEPFILTERS, the Routine row is blank instead of repeating the urgent figure. Which version is right depends on where the measure will be used, so tell the model the visual, and check it in that visual.

ALL versus REMOVEFILTERS

Inside CALCULATE, REMOVEFILTERS behaves as ALL does. The difference is that REMOVEFILTERS can only clear filters, while ALL can also return a table, so REMOVEFILTERS states the intent more plainly. The review question that matters is what’s being cleared.

-- Wrong for "share of this year's referrals": ALL on the fact table also
-- clears filters from Specialty and Date, so the denominator is every referral ever
% of Referrals =
DIVIDE ( [Referrals], CALCULATE ( [Referrals], ALL ( Referrals ) ) )

-- Right: clear the specialty filter only; the year slicer still applies
% of Referrals =
DIVIDE ( [Referrals], CALCULATE ( [Referrals], REMOVEFILTERS ( Specialty ) ) )

ALL on a fact table removes filters from the whole expanded table, which includes the dimensions it relates to. ALL ( Specialty[Specialty Name] ) goes the other way: it clears one column and leaves a filter on, say, Specialty[Division] in place. Decide which one you mean.

Blank handling

In DAX, every comparison operator except == treats BLANK as equal to 0. So “no data” and “zero” look the same:

-- Wrong: a clinic with no data is labelled as seeing patients within a week
Wait Label =
IF ( [Median Wait Weeks] = 0, "Within a week", FORMAT ( [Median Wait Weeks], "0" ) & " weeks" )

-- Right: handle BLANK first
Wait Label =
VAR Weeks = [Median Wait Weeks]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( Weeks ), BLANK (),
        Weeks = 0, "Within a week",
        FORMAT ( Weeks, "0" ) & " weeks"
    )

The other trap is [Referrals] + 0 to “show zeros”. Visuals hide rows where every measure is blank. A measure that is never blank brings back rows for combinations with no data at all, such as future months or specialties a site doesn’t run, and the visual gets slower. Keep BLANK unless a zero is meaningful, and if it is, return it only for the rows where it applies.

Division

The / operator returns an infinite value when a non-zero number is divided by BLANK or 0, and NaN when 0 is divided by BLANK. DIVIDE returns BLANK (or an alternate result you pass) when the denominator is zero or BLANK:

-- Wrong: infinity for an area with no population figure
Referral Rate per 1000 = [Referrals] / [Population] * 1000

-- Right
Referral Rate per 1000 = DIVIDE ( [Referrals], [Population] ) * 1000

Microsoft’s guidance is to use DIVIDE when the denominator is an expression that could be zero or BLANK, and / when it’s a constant, such as [Minutes] / 60.

Relationships assumed but not present

Models write DAX for the model they imagine. If Referrals relates to 'Date' on the referral date, a “clock stops per month” measure sliced by month silently counts by referral month:

-- Wrong: sliced by 'Date', this counts stopped pathways by the month they were referred
Clock Stops =
CALCULATE ( [Referrals], NOT ISBLANK ( Referrals[Clock Stop Date] ) )

-- Right: activate the inactive relationship on the clock stop date
Clock Stops =
CALCULATE (
    [Referrals],
    USERELATIONSHIP ( Referrals[Clock Stop Date], 'Date'[Date] ),
    NOT ISBLANK ( Referrals[Clock Stop Date] )
)

USERELATIONSHIP needs the relationship to exist in the model, active or inactive, and returns an error if it doesn’t, which is the good kind of failure. The NOT ISBLANK filter stays because without a date filter (on a card or at the grand total) open pathways would otherwise be counted. Also check the column types: a datetime column with a time part only matches the date table at midnight, so most rows fall into the blank row.

Performance traps

FILTER over a whole table makes the engine iterate every row, where a column predicate lets it filter the column directly. Microsoft recommends Boolean filter arguments wherever possible.

-- Slow: iterates every row of Referrals
Urgent Referrals =
CALCULATE ( [Referrals], FILTER ( Referrals, Referrals[Priority] = "Urgent" ) )

-- Faster; KEEPFILTERS keeps any existing Priority filter, as FILTER over the visible rows did
Urgent Referrals =
CALCULATE ( [Referrals], KEEPFILTERS ( Referrals[Priority] = "Urgent" ) )

Microsoft’s article makes the same rewrite. Confirm the results match in your model with the comparison query further down. FILTER is still needed when the condition uses a measure, as in FILTER ( VALUES ( 'Date'[Month] ), [Profit] > 0 ). Other traps to look for: a measure referenced inside an iterator over a large fact table (each row triggers a context transition, and duplicate rows are counted more than once), and the same measure evaluated several times in an IF where a variable would evaluate it once. For how to measure the difference, see Power BI performance starts with VertiPaq.

Testing with small known datasets

Review finds some mistakes. A fixture finds the rest.

SQL: a fixture with hand-worked answers

Build a few rows per table that include the awkward cases: a NULL status, an appointment on the last day of the month after midnight, a referral with two matching codes, a walk-in with no referral, a patient who moved. Work out the right answers by hand, then run each query against them.

sql_review_fixture.py does this with Python’s built-in sqlite3 and no other dependencies. It holds every SQL pair from this article. The core is small:

def verdict(con: sqlite3.Connection, sql: str, expected: list[tuple]) -> str:
    """PASS if the query returns exactly the expected rows, otherwise FAIL and the rows."""
    got = con.execute(sql).fetchall()
    return "PASS" if got == expected else f"FAIL {got}"

Running it gives:

march_appointments           reviewed PASS  draft FAIL [(4,)]
type2_diabetes_appointments  reviewed PASS  draft FAIL [(4,)]
attended_by_specialty        reviewed PASS  draft FAIL [('Cardiology', 2)]
referrals_never_booked       reviewed PASS  draft FAIL []
march_dna_rate               reviewed PASS  draft FAIL [(0,)]
referral_district            reviewed PASS  draft FAIL [(1, 'SA1'), (1, 'SA2'), (2, 'SA1'), (3, 'SA4'), (4, 'SA6')]

Every draft ran without error and returned something plausible. Five appointments in March came back as four, two diabetes appointments as four, a DNA rate of one in three as zero. Only the known answers showed which were wrong.

DAX: compare old and new in a query

For a DAX rewrite, define the candidate as a query-scoped measure and return only the rows where it disagrees with the current one. Run it in DAX query view in Power BI Desktop or in DAX Studio:

DEFINE
    MEASURE Referrals[Urgent Referrals v2] =
        CALCULATE ( [Referrals], KEEPFILTERS ( Referrals[Priority] = "Urgent" ) )

EVALUATE
FILTER (
    SUMMARIZECOLUMNS (
        'Date'[Year Month],
        Specialty[Specialty Name],
        "Current", [Urgent Referrals],
        "Candidate", [Urgent Referrals v2]
    ),
    NOT ( [Current] == [Candidate] )
)

An empty result means they agree for every month and specialty. Use ==, not =, so that a BLANK on one side and 0 on the other counts as a difference. For a new measure, build a small model with a few rows entered by hand, where you know what every cell of the target visual should show, and check the measure there before pointing it at real data.

For the prompts that ask a model to do this review before you do, see the AI playbook. For the prompting patterns that reduce these mistakes in the first place, see Prompting for analysts.