Verified against ChatGPT · 2026-08-10
Write a Power BI DAX measure that gets filter context right, not just one that compiles
Builds a DAX measure for a specific requirement and explicitly checks it against row-context-versus-filter-context mistakes and slicer interactions, since a DAX measure that compiles cleanly can still return a silently wrong number.
The prompt
Ready to copy — highlighted parts are example details you can swap.
Act as a senior BI developer writing a DAX measure for Power BI. DAX measures that compile without error can still be quietly wrong because of filter context and context transition mistakes — check for that specifically before presenting the measure as final. MEASURE GOAL Average revenue per active customer, where 'active' means they had at least one transaction in the selected period. DATA MODEL Fact_Sales (many-to-one to Dim_Customer on CustomerKey, many-to-one to Dim_Date on DateKey); Dim_Customer has a Status column. HOW IT WILL BE USED A matrix with customer segment on rows, month on columns, with a grand total row and column both turned on. RELATED EXISTING MEASURES [Total Revenue] and [Active Customer Count] already exist as separate measures in the model. Steps: 1. Write the DAX measure. 2. Explicitly walk through what filter context this measure will see in each place it's actually going to be used (a matrix visual sliced by date and by category, a card total, a slicer selection) and confirm the measure behaves correctly in each — not just in isolation. 3. If the measure involves an iterator function (SUMX, AVERAGEX) or CALCULATE with a context transition, state explicitly what row context is being converted to filter context and why that's the correct behavior for this specific goal, rather than assuming it's obviously fine. 4. Check whether this measure would silently produce a misleading total when totals/subtotals are shown in the same visual (a common DAX trap where a ratio or average measure sums correctly at the detail level but not at the total row) — if so, provide the corrected version that handles the total row separately. WHAT NOT TO DO Do not present a measure as final without walking through at least one concrete filter-context scenario from the usage context given — a measure that's only validated in the abstract is exactly the kind that breaks the first time someone adds it to a matrix with a total row. OUTPUT FORMAT 1. DAX measure, final version. 2. Filter-context walkthrough for each real usage scenario given. 3. Total-row behavior check and fix if needed. 4. One-sentence plain-English summary of what this measure means, for a non-technical stakeholder reading the report.
Customize
Optional — swap in your own details for the highlighted parts above.
Why this works
DAX's central difficulty — that a measure's result depends on the filter context it's evaluated inside, and that iterator functions and CALCULATE trigger context transition from row context to filter context — is precisely the kind of subtlety a model will state correctly if asked to explain DAX in the abstract, but will still get wrong in a generated measure if it isn't forced to actually trace through the specific usage scenario, because writing syntactically valid DAX and reasoning correctly about context transition are different skills that don't always co-occur in a single generation pass. Explicitly requiring the filter-context walkthrough against the real report layout (matrix with these specific rows, columns, and totals) rather than accepting the measure once it looks plausible catches the most common real-world DAX bug: a ratio-style measure (average revenue per active customer) that computes correctly for each individual cell in the matrix but produces a mathematically wrong number in the grand total row, because a total row's filter context is a UNION of everything below it, not a scaled version of one cell — a model not specifically checked against this will present the naive version as done, since it looks completely correct until someone notices the total row doesn't match a manual sanity check. Requiring related existing measures to be named forces internal consistency — a new measure reinventing a different definition of "active customer" than one that already exists elsewhere in the model is a very common source of two report pages disagreeing with each other, and stating the related measures upfront lets the model flag that divergence rather than silently duplicating logic with a subtly different filter condition.
What you get back
Avg Revenue per Active Customer := DIVIDE([Total Revenue], [Active Customer Count]) — reusing your existing measures rather than redefining 'active' independently. Filter-context walkthrough: in the matrix cells, both measures are correctly filtered by the row's segment and column's month via the model relationships; in the grand total row, DIVIDE still correctly recomputes total revenue over total active customers for that full context rather than summing the per-cell ratios, so the total row is mathematically valid, not just visually present.
Verified against
ChatGPT GPT-5.1 · 2026-08-10
Changelog
- 2026-08-10 — Initial publish, verified against ChatGPT GPT-5.1.
Need this built into your business?
If a prompt isn't enough — what Scult builds, built and maintained for you — that's Scult's day job.
EXPLORE WHAT SCULT BUILDS
