Query and Validate (DAX)
Use this workflow to validate business logic, troubleshoot measures, and analyze performance-without dumping large datasets into chat.
When to use this workflow
- validating totals and edge cases,
- confirming a measure’s logic with evidence,
- investigating performance regressions,
- checking RLS behavior (where supported).
Before you query: confirm context
“Confirm the connected model/dataset and summarize relevant tables/measures before running queries.”The query workflow (recommended)
01
Define
Success criteria
02
Propose
Plan first
03
Execute
Keep it small
04
Edge Cases
Check limits
05
Analyze
Performance
Define the question and success criteria
Example: "We expect Total Sales for last month to match Finance close within 0.5%."
Ask the assistant to propose the query first
“Write the DAX query you plan to run, explain why it’s correct, then run it and summarize results.”Keep results small and aggregated
Good:
- totals by month,
- top 10–20 contributors,
- a small set of columns.
Avoid:
- full table exports (
EVALUATE 'Sales').
Validate edge cases explicitly
“Include a case where Date is blank / there are no rows / there are returns.”For performance, use analyze mode
“Analyze performance for this query and explain the bottleneck. Recommend modeling + DAX changes.”
What to ask the LLM (prompt library)
“Run a small validation query for the last 30 days; return only aggregates.” “Show the top 20 contributors to total sales by product; return only 20 rows.” “Analyze performance for this query and explain the bottleneck.” “Get VertiPaq stats for the biggest tables and summarize the top optimization levers.” “Test access across these roles and summarize differences.”Role testing caveat (important)
- Role-based testing is generally available when roles exist in the model.
- Effective-user impersonation is Service/XMLA-only and may be tier-gated.
- If you also request deep query plan/timings, the assistant may need to rerun without impersonation.
Masking and safe sharing
If results might include sensitive values, enable masking. See Run Query for details on masking and row limit settings.
"Enable masking and keep row limits conservative."Build a validation query suite (recommended)
Troubleshooting
Reduce date range, groups, and columns; prefer aggregates; use analyze mode for performance work.
Ask for smaller output or adjust row limits (if allowed).
Query execution is disabled; ask for a query you can run outside MCP.