thinkhard.SI

Prompt repository Data & spreadsheets

DAX prompt: a Power BI measure from your data model

You need a Power BI measure that gives the right number in every visual and on the total row, and want to check it against figures you trust.

Registry no.
DAT-038
Collection
Data & spreadsheets
Laid out for
GPT
Score
10 out of 10
Blanks to fill
7

Data & spreadsheetsGPT

The prompt

Score 10 of 10, Excellent

Use it when You need a Power BI measure that gives the right number in every visual and on the total row, and want to check it against figures you trust.

  • {{measure_wanted}}
  • {{audience}}
  • {{visuals}}
  • {{definition}}
  • {{model_description}}
  • {{existing_measures}}
  • {{known_values}}
Full prompt and why it works
Prompt
# Role
You are a Power BI modeler who writes DAX measures that return the right number in every visual, slicer and total row, and who explains filter context in plain words.

# Goal
Write a DAX measure that calculates {{measure_wanted}}, for the report that {{audience}} uses, so that the figure in each visual matches the business definition below and the total row can be trusted as much as the rows above it.

# Success criteria
- It uses only the tables, columns and relationships described below, with their exact names.
- It returns the right result everywhere it will be used: {{visuals}}.
- Ratios use DIVIDE, intermediate values are held in VAR, and the measure reads top to bottom.
- Time intelligence (year to date, prior year) runs on the date table, which must be marked as a date table with one row per day and no gaps.

# Constraints
- Do not invent a column, table or relationship. If the measure needs something the model lacks, such as a date table, stop and say what to add.
- Prefer a measure to a calculated column, and a simple column filter inside CALCULATE to FILTER over a whole table.
- Do not change relationships or their filter direction. If the measure needs an inactive relationship, use USERELATIONSHIP and say so.

# Output
1. The measure in a code block, ready to paste, with a suggested format string.
2. Filter context in plain words: what the measure does in one row of the visual, on the total row, when a slicer is set, and when a filter comes from a related table. Mention context transition if the measure iterates. Keep this under 200 words.
3. Test values: a table with the columns Filter, Expected result, Where the expected figure comes from, DAX query to check. Take expected results from the figures I trust below, and write each check as a DAX query (EVALUATE with SUMMARIZECOLUMNS) to run in DAX query view or DAX Studio. For example, one row might read: "Region = North, Month = March | 12,400 | finance report | EVALUATE SUMMARIZECOLUMNS(...)". That example shows the form only.
4. Edge cases: what it returns for blanks, a zero denominator, a period with no rows, and a filter on more than one item.

# Stop rules
If the business definition can be read two ways, for example which date an order counts on when the fact table has both an order date and a ship date, ask before writing the measure. If no trusted figures are given, say which two or three I should look up to check it.

# Context
Business definition of the measure: {{definition}}

Model: tables, key columns with data types, and relationships with their direction:
"""
{{model_description}}
"""

Existing measures I already use:
"""
{{existing_measures}}
"""

Figures I already trust, for checking: {{known_values}}

Variables

{{measure_wanted}}
What the measure should calculate, for example "gross margin % by product" or "active customers in the last 90 days".
{{audience}}
Who reads the report, for example "the regional sales managers".
{{visuals}}
Where it will appear, for example "a matrix by region and month, a card, and a line chart by week".
{{definition}}
The business definition in plain words, including what is excluded, for example "revenue after refunds, excluding internal orders".
{{model_description}}
The tables, the columns the measure needs with their data types, and each relationship with its direction and whether it is active.
{{existing_measures}}
Measures already in the model that this one could reuse, with their DAX. Write "none" if there are none.
{{known_values}}
Figures you already trust from another source, for example "North, March: 12,400 in the finance report". Write "none" if you have none.

Why it works

  • If unsure. A definition that can be read two ways, such as order date against ship date, stops the work for a question instead of producing a measure built on a guess.
  • An example. Each test value comes with a DAX query to run, so the measure is checked against figures you already trust rather than taken on faith.
  • Format. Filter context is explained for a row, the total row, a slicer and a related-table filter, the places where DAX measures usually surprise people.

How to use it

  1. Press Fill in blanks and type your details into each blank. What you type stays in this browser.
  2. Copy the filled prompt into the AI tool you already use, such as ChatGPT, Claude or Gemini, or open it in the Grader or the Prompt Tester here.
  3. Before you rely on it, try it on three or four real examples. The Prompt Tester runs them all at once and shows which ones go wrong.
Use it from code

Get this prompt as JSON from the free REST API, with your own text in the blanks, or let an AI assistant fetch it through the MCP server. No key.

curl -s https://thinkhard.si/api/v1/prompts/power-bi-dax-measure

With the MCP server added to Claude, ChatGPT, Cursor or VS Code, call get_prompt with {"prompt": "power-bi-dax-measure"}, or just ask for it by name.