Ravindra BagaleCourses & study guides मराठी Track your progress

13. DAX: Data Analysis Expressions

13.27 DAX Query View

By Last updated:

DAX query view in Power BI with example — in 30 seconds

What: DAX query view lets you write a DAX query that returns a table. It is a test sheet inside Power BI Desktop.

Why: A card shows a strange number. You want to see the numbers behind it without building new visuals.

Tiny example: EVALUATE SUMMARIZECOLUMNS(Orders[City], "Sales", [Total Sales]) returns one row per city.

First look at the data (before → after):

DAX query view in Power BI with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Orders (practice data, 6 rows)

City Amount (₹)
Pune 400
Pune 0
Mumbai 600
Mumbai 500
Nashik 300
Pune 200

Formula / click path

EVALUATE
SUMMARIZECOLUMNS(Orders[City], "Sales", [Total Sales], "Orders", [Total Orders])
ORDER BY [Sales] DESC

After — Result grid in DAX query view

Orders[City] [Sales] [Orders]
Mumbai 1100 2
Pune 600 3
Nashik 300 1

Do it step by step:

  1. Click the DAX query view icon in the left bar.
  2. Type the query. Every query starts with EVALUATE.
  3. Click Run.
  4. Read the grid: Mumbai 1100, Pune 600, Nashik 300.
  5. To test a new measure, put DEFINE MEASURE Orders[Test AOV] = DIVIDE([Total Sales], [Total Orders]) above EVALUATE.
  6. When it works, click Update model with changes to save the measure.

What appears on screen after these steps — sample layout, fictional data

What you should see on screen after the steps (sample layout — fictional data, not a Microsoft screenshot).

A report visual is just a dress over a DAX query. In DAX query view you can write that query yourself. Use this view for debugging.

Open DAX query view from the left icon bar (section 2.2). You write a query, choose Run, and read a result grid. Nothing is saved to the model until you explicitly add it (the button has been labelled Update model with changes in recent Desktop builds; the label is version-dependent).

EVALUATE runs a table expression and shows the rows. ORDER BY sorts that result. DEFINE introduces query-only measures, columns or tables that exist for this query, which is how you test a measure before you trust it on a page.

DEFINE
    MEASURE Orders[Test AOV] =
        DIVIDE([Total Sales], [Delivered Orders])
EVALUATE
SUMMARIZECOLUMNS(
    DarkStore[City],
    "Sales", [Total Sales],
    "AOV", [Test AOV]
)
ORDER BY [Sales] DESC

MEASURE inside DEFINE overrides a model measure of the same name for this query only, or creates a temporary one. That is the safe way to try a fix. SUMMARIZECOLUMNS here is the query-language group-by: city, then two numbers.

Steps – test before you publish

  1. Open DAX query view. Paste the query above.
  2. Run it. Confirm Pune, Mumbai, Nashik, Sambhaji Nagar, Kolhapur and Nagpur appear only if they have rows. A blank AOV means delivered orders were zero; that is DIVIDE, not a broken query.
  3. Add a filter to see one city: FILTER(VALUES(DarkStore[City]), DarkStore[City] = "Kolhapur") as the first argument's replacement, or use TREATAS / a calculate pattern you already trust. Simpler: look at the Kolhapur row in the full result.
  4. Only when the number matches a Table-view spot check, create the real measure with Modeling › New measure. Do not paste DEFINE into the measure editor. The measure editor wants the expression only.

Worked example. You think Kolhapur AOV is wrong. Query view shows Sales ₹300 and Delivered Orders blank, so AOV is blank. The page card showed 0 because a visual format or a + 0 was hiding the blank. The query told the truth. Fix the card, not the sales table.

Quick queries: right-click a measure in this view (in versions that offer it) and choose a define-and-evaluate starter. If you do not see that menu, paste the pattern above. The icons move; EVALUATE does not.

Ravindra Bagale's Tip

Many students debug by adding ten card visuals. Query view is faster and it does not change the report. And do not assume DEFINE MEASURE is saved to the model without clicking the button. Check a wrong number here before you put it on a page. Remember this rule.

Loading DAX practice tasks...

  1. Show the Shop table (Easy): Query: return the whole Shop table.
  2. English shops (Easy): Query: return Shop rows where Country is "England".
  3. Menu items, sorted (Easy): Query: return the different Item values of Sales, sorted A to Z.
  4. Total cups in one row (Easy): Query: return one row with a column "Cups" holding the SUM of Sales[Cups].
  5. Big sales (Medium): Query: return Sales rows with Cups of 20 or more, sorted by SaleID.
  6. Revenue by city (Medium): Query: one row per Shop City with a column "Revenue" = [Revenue], sorted by City.
  7. Cups per barista (Medium): Query: each Barista with a column "Cups" = CALCULATE of SUM of Sales[Cups], sorted by Barista.
  8. Sales count by pay mode (Medium): Query: one row per PayMode with "Sales" = COUNTROWS of Sales, sorted by PayMode.
  9. Top 2 items (Hard): Query: the 2 Items with the highest [Revenue], sorted by Item.
  10. London baristas (Hard): Query: the different Barista values for sales in London shops, sorted by Barista.

See all Power BI practice tasks

Ravi Sir's Cards for this lesson

DAX query view. Building a visual just to check a number is slow.Revision card

मराठी cards