MSAZ-319 · Lesson 7 of 12 · 20 min

Reading DAX, SQL and PySpark in questions

▶ Video coming soon A video version of this lesson is on the way.

Many DP-600 items show a block of code and ask what it returns, which line is wrong, or which keyword completes it. The candidate does not run anything; the skill is reading. On Monday, November 30, Marisol gave Keiko a stack of short listings in four languages, DAX, T-SQL, PySpark and KQL, and asked her to say in one sentence what each did before looking at any options. Keiko writes DAX and T-SQL every week, reads PySpark a few times a month and had written only three KQL queries, so the evening showed clearly where her reading was slow.

This lesson works through one listing in each language, then gives a method for reading code under time pressure. The listings are short on purpose: exam items rarely show more than fifteen lines, and the hard part is usually one keyword, one filter or one join type.

A method for reading code in an item

Marisol's method has four steps. First, find the output: in DAX, the RETURN expression or the measure's result; in SQL, the SELECT list; in PySpark, the last write or display; in KQL, the last operator. Second, find the source and the filters: which table, which WHERE, filter or where clause, and which joins. Third, find the grouping: GROUP BY, groupBy, summarize by, or the filter context in DAX. Fourth, look for the one detail the item is about, which is usually a join type, a null, a date boundary or a filter that applies at the wrong step. Saying the result in plain words before reading the options stops a plausible option from deciding for you.

Where to look first in each language.
LanguageOutputSource and filtersGroupingCommon trap
DAXRETURN or measure resultCALCULATE filters, FILTERFilter context of the visualALL removing a filter the item needs
T-SQLSELECT listFROM, JOIN, WHEREGROUP BY, HAVINGINNER JOIN dropping unmatched rows
PySparkwrite, saveAsTable, displayread, filter, joingroupBy, aggOverwrite where append was meant
KQLLast operatorTable, wheresummarize bybin size or ago() window

DAX

DAX items usually turn on filter context. The measure below counts the stores that sold more than a target in the selected period.

A DAX measure that counts stores above a sales target.
Stores Above Target =
VAR Target = 250000
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( 'Store'[Store Key] ),
            [Net Sales] > Target
        )
    )

Read it with the method. The output is a count of rows. The source is the list of store keys visible in the current filter context, from VALUES. The filter keeps only stores whose net sales exceed 250,000; because [Net Sales] is a measure, it is evaluated for each store by context transition. The grouping is whatever the visual groups by: on a card with no other filters it counts across all stores; in a table by region it counts within each region. An item might ask what changes if VALUES is replaced by ALL('Store'[Store Key]); the answer is that the count would ignore filters on the store table, such as a region slicer, which is usually wrong.

T-SQL

SQL items often test joins and nulls. The query below lists stores and their sales for November 2026.

A T-SQL query that totals November sales by store.
SELECT s.store_name,
       SUM(f.net_amount) AS net_sales
FROM dbo.dim_store AS s
LEFT JOIN dbo.fact_sales AS f
       ON f.store_key = s.store_key
WHERE f.sale_date >= '2026-11-01'
  AND f.sale_date <  '2026-12-01'
GROUP BY s.store_name;

The output is one row per store with its net sales. The join is a LEFT JOIN, which suggests that stores without sales should appear with a null total. But the WHERE clause filters on columns of fact_sales, and for a store with no sales those columns are null, so the WHERE removes it. The LEFT JOIN behaves like an INNER JOIN. To keep stores without sales, the date conditions move into the ON clause. This is one of the most common SQL traps on the exam, and Keiko missed it on the diagnostic.

Two panels. On the left, a LEFT JOIN with the date filter in WHERE returns only stores with November sales. On the right, the same join with the date filter in the ON clause returns every store, with a null total for stores without sales.
Where a filter goes changes the result of a LEFT JOIN.

PySpark

PySpark items usually show a short transformation in a notebook. The listing below cleans the day's sales lines and adds them to a lakehouse table.

A PySpark cell that cleans sales lines and appends them to a Delta table.
df = spark.read.table("lh_raw.sales_lines")
clean = (df.filter(df.quantity > 0)
           .dropDuplicates(["receipt_id", "line_no"])
           .withColumn("net_amount", df.unit_price * df.quantity - df.discount_amt))
clean.write.mode("append").format("delta").saveAsTable("lh_clean.sales_lines")

The output is an append to the Delta table lh_clean.sales_lines. The source is lh_raw.sales_lines, filtered to positive quantities, which removes returns. Duplicates are removed on the receipt and line number. The new column computes the net amount. An item could ask what happens if the cell runs twice for the same day: because the mode is append and nothing checks for rows already loaded, the day's lines are added twice. The fixes are a MERGE on the key, or overwriting only the day's partition. Another item could ask why returns disappear from the clean table, which is the quantity filter.

KQL

KQL items appear in questions about eventhouses and real-time data. The query below looks for tills that have stopped sending events.

A KQL query that lists tills silent for more than ten minutes.
TillEvents
| where Timestamp > ago(30m)
| summarize LastEvent = max(Timestamp) by StoreId, TillId
| where LastEvent < ago(10m)
| project StoreId, TillId, LastEvent
| order by LastEvent asc

The output is a list of stores and tills with their last event time, oldest first. The source is events from the last 30 minutes. The grouping is by store and till, keeping the latest timestamp. The second where keeps tills whose latest event is more than ten minutes old. The trap is the first filter: a till that has been silent for more than 30 minutes has no events in the window, so it does not appear at all. An item might ask how to include it, and the answer is to compare against a list of all tills rather than only the tills that sent events.

Seeing the join trap with real rows

Keiko understood the LEFT JOIN trap once she saw it with a handful of rows. Marisol wrote the short listing below, which joins three stores to four sales lines in plain Python, first with the date filter applied after the join, as a WHERE clause would, then with the filter applied inside the join, as an ON clause would. It runs in the browser.

Python Example · A LEFT JOIN with the date filter in two places
stores = ["Store 101", "Store 102", "Store 103"]
sales = [("Store 101", "2026-11-03", 1200), ("Store 101", "2026-10-28", 800),
         ("Store 102", "2026-11-12", 950), ("Store 103", "2026-10-15", 400)]

def in_november(day):
    return "2026-11-01" <= day < "2026-12-01"

# Filter after the join, like WHERE
after = {}
for store in stores:
    matches = [s for s in sales if s[0] == store] or [(store, None, None)]
    for _, day, amount in matches:
        if day is not None and in_november(day):
            after[store] = after.get(store, 0) + amount
print("Filter in WHERE:", after)

# Filter inside the join, like ON
inside = {}
for store in stores:
    matches = [s for s in sales if s[0] == store and in_november(s[1])]
    inside[store] = sum(m[2] for m in matches) if matches else None
print("Filter in ON:   ", inside)
Output
Filter in WHERE: {'Store 101': 1200, 'Store 102': 950}
Filter in ON:    {'Store 101': 1200, 'Store 102': 950, 'Store 103': None}

What this code does

The listing sets up three stores and four sales lines, one of them for Store 103 in October only. The first loop joins each store to its sales and keeps a row only if its date is in November, which is what a WHERE clause does after a left join, so Store 103 disappears. The second loop applies the November test while matching, which is what an ON clause does, so Store 103 stays in the result with no total.

The first result has two stores; the second has all three, with Store 103's total empty. That is exactly the difference between the two versions of the SQL query in the lesson. The same pattern appears in DAX when a filter is applied to the fact table instead of through the dimension, and in PySpark when a filter on the right-hand table follows a left join instead of preceding it.

Power Query M

Although M is not named in the outline as often as the other languages, dataflow items sometimes show a few steps of a query. Each step is a line in a let expression that refers to the previous one by name, and the last name after in is the output.

A Power Query M query that loads supplier prices effective up to today.
let
    Source = Csv.Document(File.Contents("supplier_prices.csv"), [Delimiter = ","]),
    Promoted = Table.PromoteHeaders(Source),
    Typed = Table.TransformColumnTypes(Promoted, {{"unit_cost", type number}, {"effective_date", type date}}),
    Current = Table.SelectRows(Typed, each [effective_date] <= Date.From(DateTime.LocalNow()))
in
    Current

Read it with the same method. The output is the step called Current. The source is a CSV file of supplier prices with headers promoted. The types are set before the filter, which matters: comparing text to a date would fail or behave unexpectedly. The filter keeps prices effective up to today. An item might ask why the query does not fold to the source, and for a CSV file the answer is that files cannot fold at all; folding applies to sources such as databases.

DAX windowing functions

The newer windowing functions, WINDOW, OFFSET and INDEX, appear in the outline and in items that ask for a running total or a comparison with the previous row. OFFSET(-1, ...) returns the previous row in a given order, so a measure that subtracts the previous month's sales from the current month's can be written without date intelligence functions. WINDOW returns a range of rows, such as the three months up to the current one for a rolling average. Each takes an ORDERBY and, optionally, a PARTITIONBY that restarts the order within each group, such as each store. Keiko had not used them before; she found it easiest to think of them as SQL window functions written in DAX, with ORDERBY and PARTITIONBY playing the same roles as ORDER BY and PARTITION BY in an OVER clause.

DAX windowing functions and their closest T-SQL equivalents.
DAXClosest T-SQLTypical use
OFFSET(-1, ...)LAG()Change from the previous month
WINDOW(-2, REL, 0, REL, ...)AVG() OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)Three-month rolling average
INDEX(1, ...)FIRST_VALUE()First month in the period
RANKXRANK()Ranking stores by sales

Reading a windowing function in an item comes down to three questions: which rows are ordered, in which order, and where the order restarts. A missing PARTITIONBY is the usual mistake: without it, a running total by store runs across all stores instead of restarting for each one. Keiko now checks for PARTITIONBY first whenever an item shows a windowing function grouped by store or region, and she adds a line to her error log every time she misses it in practice, so that by the September timed sets the habit is automatic.

Worked problem

Saying a listing in one sentence

Summarize the KQL query above in one sentence before reading any options, then name one case it misses.

Show solution
  1. Output: stores and tills with their last event, oldest first.
  2. Source and filter: events in the last 30 minutes.
  3. Grouping: by store and till, latest timestamp.
  4. Second filter: latest event more than ten minutes ago.
  5. Missed case: tills silent for more than 30 minutes, which have no events in the window.

Answer: It lists tills that sent events in the last half hour but none in the last ten minutes; tills silent for longer than half an hour are missed.

Common mistakes

  • MistakeAssume a LEFT JOIN always keeps unmatched rows.

    InsteadA WHERE filter on the right table's columns removes them; move the filter into ON.

  • MistakeReplace VALUES with ALL without thinking about slicers.

    InsteadALL ignores filters on the column, so slicers stop affecting the count.

  • MistakeAppend mode is safe to rerun.

    InsteadRerunning an append duplicates the rows; use MERGE or overwrite a partition.

  • MistakeA KQL summarize finds missing items.

    InsteadIt can only group rows that exist; compare against a full list to find what is missing.

  • MistakeRead options before the code.

    InsteadSay what the code does first, then read the options.

Summary and key terms

  • Read code in four steps: output, source and filters, grouping, then the one detail the item is about.
  • DAX items turn on filter context, context transition and functions such as VALUES and ALL.
  • SQL items often test join types, where filters sit, and nulls.
  • PySpark items test filters, deduplication, write modes and what happens on rerun.
  • KQL items test where, summarize by, bin() and the time window chosen with ago().
  • Say the result in plain words before reading the options.
Context transition
DAX turning the current row of an iterator into a filter when a measure is evaluated inside it.
LEFT JOIN
A join that keeps every row from the left table, with nulls where the right table has no match.
Write mode
The option that decides whether a Spark write appends, overwrites or fails when the table exists.
summarize
The KQL operator that aggregates rows, grouped by the columns after by.
ago()
A KQL function that returns a time a given interval before now.

Practice problems

Work each problem on paper first, then reveal the answer to check.

  1. In the T-SQL query, why do stores with no November sales disappear?

    Show answer

    The WHERE filter on fact_sales columns removes rows where those columns are null.

  2. What happens if the PySpark cell runs twice for the same day?

    Show answer

    The day's lines are appended twice.

  3. In the DAX measure, what does VALUES('Store'[Store Key]) return?

    Show answer

    The store keys visible in the current filter context.

  4. Which tills does the KQL query miss?

    Show answer

    Tills silent for more than 30 minutes.

  5. What is the first step of the reading method?

    Show answer

    Find the output.

Lesson quiz · 5 questions

Score 60% or more to complete this lesson.

Take the quiz