Microsoft Power BI Quiz: Debugging Dax
10 questions · exam conditions
0:00
Debugging DaxQuestion 1 of 10

A model contains Sales[Quantity], Sales[ProductKey], and Product[List Price]. Each product has one current list price. The following measure returns expected values when a matrix is grouped by product, but the grand total is incorrect:

Revenue = SUM(Sales[Quantity]) * AVERAGE(Product[List Price])

The grand total must equal the sum of quantity multiplied by list price for every sales row.

Which replacement measure should you use?

SUMX(Sales, Sales[Quantity] * RELATED(Product[List Price]))
SUM(Sales[Quantity]) * CALCULATE(AVERAGE(Product[List Price]))
SUMX(VALUES(Product[ProductKey]), SUM(Sales[Quantity]) * MAX(Product[List Price]))
SUM(Sales[Quantity]) * DIVIDE(SUMX(Product, Product[List Price]), DISTINCTCOUNT(Product[ProductKey]))
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Debugging Dax

Practice Debugging Dax in Microsoft Power BI with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Debugging Dax, giving you a quick way to practice the rules, question types, and explanations that matter most for Microsoft Power BI.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A model contains Sales[Quantity], Sales[ProductKey], and Product[List Price]. Each product has one current list price. The following measure returns expected values when a matrix is grouped by product, but the grand total is incorrect:

Revenue = SUM(Sales[Quantity]) * AVERAGE(Product[List Price])

The grand total must equal the sum of quantity multiplied by list price for every sales row.

Which replacement measure should you use?

  1. SUMX(Sales, Sales[Quantity] * RELATED(Product[List Price])) (correct answer)
  2. SUM(Sales[Quantity]) * CALCULATE(AVERAGE(Product[List Price]))
  3. SUMX(VALUES(Product[ProductKey]), SUM(Sales[Quantity]) * MAX(Product[List Price]))
  4. SUM(Sales[Quantity]) * DIVIDE(SUMX(Product, Product[List Price]), DISTINCTCOUNT(Product[ProductKey]))
Explanation: Whenever you see a measure that works at the row level but breaks at the grand total, the culprit is almost always context collapse — aggregations like AVERAGE recalculate across the entire dataset when the filter context expands at the grand total row, rather than summing the already-computed row values. The original measure uses AVERAGE(Product[List Price]) at the grand total, which averages all list prices across all products — not a weighted average by quantity. This produces a mathematically incorrect result. Answer A is correct because SUMX(Sales, Sales[Quantity] * RELATED(Product[List Price])) iterates row-by-row over the Sales table. For each individual sales row, it multiplies that row's quantity by the related product's list price, then sums all those products. This works correctly at every level — per product and at the grand total — because it never relies on a context-sensitive average. Answer B is wrong for the same reason as the original measure. Wrapping AVERAGE in CALCULATE doesn't change what's being averaged — at the grand total, it still averages all list prices without weighting by quantity. Answer C iterates over distinct products using VALUES, not over individual sales rows. SUM(Sales[Quantity]) inside the loop aggregates all quantities for that product correctly, but using MAX(Product[List Price]) is an unnecessary workaround, and this pattern is more fragile and roundabout than a direct row iteration. Answer D manually reconstructs an average using SUMX and DISTINCTCOUNT, which is an unweighted average — the same fundamental flaw as the original. As a study tip: when a measure misbehaves at the grand total, reach for SUMX with RELATED to iterate at the row level — that's the standard DAX pattern for row-by-row calculations across related tables.

Question 2

You define the measure [Sales Amount] = SUM(Sales[Amount]). The following measure correctly sums sales by customer:

Customer Total = SUMX(VALUES(Customer[CustomerKey]), [Sales Amount])

During debugging, you replace [Sales Amount] with its underlying expression:

Customer Total Test = SUMX(VALUES(Customer[CustomerKey]), SUM(Sales[Amount]))

The test measure repeats the same overall sales amount for each customer and produces an inflated total.

Which change most directly restores the original evaluation behavior?

  1. Replace VALUES(Customer[CustomerKey]) with ALL(Customer[CustomerKey]) inside the iterator.
  2. Wrap SUM(Sales[Amount]) in CALCULATE so the customer row context becomes filter context. (correct answer)
  3. Wrap the complete SUMX expression in CALCULATE to preserve the customer row context.
  4. Replace SUM(Sales[Amount]) with SUMX(Sales, Sales[Amount]) inside the customer iterator.
Explanation: Whenever you see a question about iterators like SUMX in DAX, the critical concept to keep in mind is the row context vs. filter context distinction. Iterators create a row context as they loop, but SUM and other aggregation functions respond only to filter context — they don't automatically "see" the current row. In the original measure, [Sales Amount] is a measure reference, and DAX automatically wraps measure references inside iterators with an implicit CALCULATE. That implicit CALCULATE converts the row context established by SUMX (the current CustomerKey) into an equivalent filter context, so SUM(Sales[Amount]) correctly filters to only that customer's rows. When you inline SUM(Sales[Amount]) directly, you remove that implicit conversion — the row context never becomes a filter context, and SUM ignores the current customer entirely, summing all sales every iteration. Answer B is correct because explicitly wrapping SUM(Sales[Amount]) in CALCULATE restores that context transition, replicating what the implicit CALCULATE was doing automatically. A is wrong because switching to ALL(Customer[CustomerKey]) removes filters rather than adding them — it would make the problem worse, not fix it. C is wrong because wrapping the entire SUMX in CALCULATE doesn't help; the context transition needs to happen inside the iterator, at the point where SUM evaluates. D is wrong because replacing SUM with SUMX(Sales, Sales[Amount]) introduces a nested iterator but still doesn't transition the outer row context into filter context. A useful rule of thumb: any time you manually inline a measure inside an iterator, ask yourself "where did the implicit CALCULATE go?" — and add it back explicitly.

Question 3

A report has a year slicer and a product-category slicer. A matrix displays one row for each selected category. The following measure should show each row's percentage of sales across only the categories retained by the category slicer while continuing to honor the selected year:

Category Share = DIVIDE([Sales Amount], <denominator>)

Which expression should replace <denominator>?

  1. CALCULATE([Sales Amount], ALL(Product[Category]))
  2. CALCULATE([Sales Amount], ALLSELECTED(Product[Category])) (correct answer)
  3. CALCULATE([Sales Amount], ALLEXCEPT(Product, Product[Category]))
  4. CALCULATE([Sales Amount], REMOVEFILTERS(Product))
Explanation: When writing percentage-of-total measures in Power BI, the core challenge is controlling which filters get removed for the denominator while preserving others. Ask yourself: "Which filters should the denominator ignore, and which should it respect?" Here, you want the denominator to sum sales across all selected categories (ignoring the per-row category filter), but still respect the year slicer. ALLSELECTED(Product[Category]) does exactly this — it removes the current row context filter on Category while keeping any external slicer selections, including the year filter, intact. This makes option B the correct denominator, giving each row its share of the category-slicer-visible total within the chosen year. Option A uses ALL(Product[Category]), which removes the category filter entirely — including what the category slicer selected. This means the denominator includes all categories in the model, not just the ones the user filtered to, producing incorrect percentages that won't sum to 100% within the slicer selection. Option C, ALLEXCEPT(Product, Product[Category]), keeps only the Category filter and removes everything else — including the year slicer. This breaks the year context, so your totals span all years regardless of what the year slicer shows. Option D, REMOVEFILTERS(Product), strips every filter on the Product table entirely, which collapses both the category row context and the category slicer selection, making the denominator the grand total across all categories and years. Study tip: Think of ALLSELECTED as "respect the slicer, ignore the row context." Whenever a measure needs to calculate a share within what a slicer has filtered, ALLSELECTED is almost always your tool.

Question 4

A measure is intended to return sales for blue products only when blue is included in the current product-color context. If a visual or slicer restricts the context to red, the measure should return blank. The current measure still returns blue sales under a red-only selection:

Blue Sales = CALCULATE([Sales Amount], Product[Color] = "Blue")

How should you modify the measure?

  1. Use CALCULATE([Sales Amount], KEEPFILTERS(Product[Color] = "Blue")). (correct answer)
  2. Use CALCULATE([Sales Amount], ALL(Product[Color]), Product[Color] = "Blue").
  3. Use CALCULATE([Sales Amount], REMOVEFILTERS(Product[Color]), Product[Color] = "Blue").
  4. Use CALCULATE([Sales Amount], FILTER(ALL(Product), Product[Color] = "Blue")).
Explanation: When working with CALCULATE in DAX, it's essential to understand how filter arguments interact with the existing filter context. By default, CALCULATE replaces any existing filter on a column with its own filter argument. This means Product[Color] = "Blue" overwrites whatever color filter a slicer or visual has already applied — which is exactly why the original measure ignores a red-only selection and still returns blue sales. The fix is KEEPFILTERS, making A the correct answer. Wrapping the filter condition in KEEPFILTERS(Product[Color] = "Blue") tells DAX to intersect the new filter with the existing context rather than replace it. If the current context is already restricted to red, the intersection of {Red} and {Blue} is empty, so the measure correctly returns blank. B and C are wrong for the same reason: ALL(Product[Color]) and REMOVEFILTERS(Product[Color]) both clear the existing color filter before applying the blue filter, which is essentially what the original broken measure already does — just written more explicitly. You'd still get blue sales regardless of the slicer. D uses FILTER(ALL(Product), ...), which also clears all existing filters on the Product table before evaluating, so it suffers from the same problem as B and C — the slicer context is ignored entirely. A useful mental model: think of KEEPFILTERS as a logical AND with the current context, while a plain filter argument acts like an OR-replace. On the Power BI exam, whenever a measure must respect an existing slicer selection rather than override it, KEEPFILTERS is almost always the right tool.

Question 5

A one-to-many relationship runs from Customer to Sales. In the Customer table, the calculated column Lifetime Sales = [Sales Amount] returns sales for the current customer. However, the calculated column Lifetime Sales Test = SUM(Sales[Amount]) returns the same grand total for every customer. [Sales Amount] is defined as SUM(Sales[Amount]).

What best explains the different results?

  1. A measure reference receives an implicit CALCULATE, which transitions the customer row context into filter context. (correct answer)
  2. A measure reference reverses the relationship temporarily, allowing each sales row to filter its related customer.
  3. A naked SUM ignores model relationships in calculated columns, while measures always enforce those relationships.
  4. A measure reference evaluates after the calculated column is stored, while a naked aggregation evaluates before relationships load.
Explanation: Whenever you see a question about calculated columns versus measures in Power BI, the key concept to hold in your mind is row context vs. filter context — and crucially, how one transitions into the other. In a calculated column, DAX iterates row by row through the table. When you write SUM(Sales[Amount]) directly inside a calculated column on the Customer table, DAX has no automatic mechanism to restrict that aggregation to only the related sales rows — it simply sums the entire Sales column, producing the grand total for every customer. This is the trap in Lifetime Sales Test. When you instead reference a measure like [Sales Amount], something special happens: the measure reference implicitly wraps itself in CALCULATE. That implicit CALCULATE performs context transition — it converts the current row context (the specific customer row being evaluated) into an equivalent filter context. Now SUM(Sales[Amount]) inside the measure runs with a filter on Customer, and the one-to-many relationship propagates that filter down to Sales, returning only that customer's sales. This makes A correct. B is wrong because relationships don't "reverse temporarily" — filter context flows from the one-side (Customer) to the many-side (Sales) as normal; no reversal occurs. C is wrong because it implies SUM always ignores relationships, which is false — the real issue is the absence of context transition, not relationship awareness. D is fabricated; calculated columns and measures don't differ based on storage timing or relationship loading order. Your study tip: memorize the phrase "measure reference = implicit CALCULATE = context transition." That chain explains a large family of DAX exam questions.

Question 6

A matrix uses a hierarchy with Product[Category] above Product[Subcategory]. A measure should return [Subcategory Margin] only on subcategory rows and [Category Margin] on category subtotal rows. The current test uses HASONEVALUE(Product[Subcategory]). When a slicer leaves only one subcategory in a category, the category subtotal incorrectly uses [Subcategory Margin].

Which condition should replace HASONEVALUE(Product[Subcategory])?

  1. ISFILTERED(Product[Subcategory])
  2. HASONEFILTER(Product[Subcategory])
  3. ISINSCOPE(Product[Subcategory]) (correct answer)
  4. SELECTEDVALUE(Product[Subcategory]) <> BLANK()
Explanation: When working with DAX measures inside matrix visuals, the critical question is: how did this value end up in the filter context? A cell might show a single subcategory not because the visual is drilling into that level, but because a slicer has filtered everything else out. These two situations look identical to functions that inspect filter results, but they represent completely different positions in the hierarchy. ISINSCOPE(Product[Subcategory]) is the right tool here because it checks whether a column is currently acting as a grouping axis in the visual — meaning the matrix has explicitly expanded to that level. On a category subtotal row, even if a slicer leaves only one subcategory visible, Subcategory is not the active scope — Category is. So ISINSCOPE correctly returns FALSE on subtotal rows regardless of slicer state, and your measure branches to [Category Margin] as intended. Option A, ISFILTERED(Product[Subcategory]), returns TRUE whenever any filter exists on that column — including slicer filters — so it fires incorrectly on category subtotal rows when a slicer is active. Option B, HASONEFILTER(Product[Subcategory]), checks whether exactly one filter value exists directly on the column; a slicer selecting one subcategory satisfies this even on a subtotal row, causing the same bug as your original HASONEVALUE. Option D, SELECTEDVALUE(Product[Subcategory]) <> BLANK(), behaves similarly to HASONEVALUE — it returns non-blank when context resolves to a single value, which a slicer can force even at the subtotal level. The study tip: whenever a measure must behave differently based on visual hierarchy level (not filter state), reach for ISINSCOPE. It is the only DAX function that reflects the matrix's actual drill position.

Question 7

A matrix is grouped by Product[Category]. The report also contains product-brand, date, and region slicers. The following measure is intended to divide each row's sales by sales for all products while retaining the current date and region filters:

Percent of All Products = DIVIDE([Sales Amount], CALCULATE([Sales Amount], ALL(Product[ProductKey])))

The measure returns an unexpectedly high percentage because category and brand filters remain active.

Which denominator should you use to ignore every product-table filter while retaining date and region filters?

  1. CALCULATE([Sales Amount], ALLEXCEPT(Product, Product[Category]))
  2. CALCULATE([Sales Amount], ALLSELECTED(Product))
  3. CALCULATE([Sales Amount], REMOVEFILTERS(Product[ProductKey]))
  4. CALCULATE([Sales Amount], ALL(Product)) (correct answer)
Explanation: When building percent-of-total measures in DAX, your key question is: which filters should the denominator respect, and which should it ignore? The denominator must remove all product-table filters — not just one column — while keeping date and region filters intact. ALL(Product) is the right tool here, making D the correct answer. It clears every filter on the Product table regardless of source — whether from the matrix rows, the brand slicer, or any relationship — while leaving filters on other tables (like your date and region dimensions) completely untouched. The result is true "sales for all products under the current date and region context." Here's why the other options fall short. A uses ALLEXCEPT(Product, Product[Category]), which removes most product filters but preserves the category filter — meaning each row's denominator is scoped to its own category, so percentages will sum to 100% within each category rather than across all products. B uses ALLSELECTED(Product), which only removes filters applied within the visual, but respects any outer filter context like slicers — so the brand slicer would still constrain the denominator, producing inflated percentages. C uses REMOVEFILTERS(Product[ProductKey]), which only clears the filter on that single column; the category and brand columns remain filtered, replicating the exact bug described in the question. A reliable study tip: when you need a denominator that represents "everything" in a table, reach for ALL(Table) rather than ALL(Table[Column]). Column-level ALL functions are surgical — they leave other columns in that table filtered, which is usually not what percent-of-total measures need.

Question 8

You create the following measure:

Prior-Year Sales = VAR CurrentSales = [Sales Amount] RETURN CALCULATE(CurrentSales, DATEADD(Date[Date], -1, YEAR))

For each year, the measure returns the current year's sales instead of the prior year's sales.

Which revision most directly fixes the measure?

  1. VAR CurrentSales = [Sales Amount] RETURN CALCULATE(CurrentSales, ALL(Date))
  2. VAR CurrentSales = CALCULATE([Sales Amount]) RETURN CALCULATE(CurrentSales, DATEADD(Date[Date], -1, YEAR))
  3. CALCULATE([Sales Amount], DATEADD(Date[Date], -1, YEAR)) (correct answer)
  4. SUMX(DATEADD(Date[Date], -1, YEAR), CurrentSales)
Explanation: When working with DAX variables and CALCULATE, you need to understand a critical concept: variables are evaluated at the point of definition, not at the point of use. This is the heart of what this question tests. In the original measure, VAR CurrentSales = [Sales Amount] captures the sales value immediately in the current filter context — before CALCULATE has a chance to shift the date context backward by one year. When CALCULATE then applies the DATEADD filter, it's trying to modify context around a variable that's already been resolved to a fixed number. The date shift has no effect on CurrentSales, so the measure simply returns the current year's sales every time. The cleanest fix is C: CALCULATE([Sales Amount], DATEADD(Date[Date], -1, YEAR)). This removes the variable entirely and lets CALCULATE do its job — it shifts the filter context to the prior year before evaluating [Sales Amount], which is exactly what you want. A is wrong because replacing DATEADD with ALL(Date) removes all date filters rather than shifting them, which would return total sales across all years — not prior-year sales. B is a common misconception. Wrapping the variable in CALCULATE([Sales Amount]) doesn't help, because the variable is still resolved in the current context before the outer CALCULATE applies the date shift. Variables captured outside of a CALCULATE call cannot be retroactively affected by it. D is syntactically broken — CurrentSales is not defined in that scope, making it an invalid reference. A useful rule of thumb: if a variable depends on a filter you plan to change with CALCULATE, define that variable inside the CALCULATE call or skip the variable altogether.

Question 9

A model has an active relationship from Date[Date] to Sales[OrderDate] and an inactive relationship from Date[Date] to Sales[ShipDate]. [Sales Amount] uses the active order-date relationship. A new measure must report sales by ship date when users filter the Date table.

Which measure should you create?

  1. CALCULATE([Sales Amount], USERELATIONSHIP(Sales[OrderDate], Date[Date]))
  2. CALCULATE([Sales Amount], USERELATIONSHIP(Sales[ShipDate], Date[Date])) (correct answer)
  3. CALCULATE([Sales Amount], CROSSFILTER(Sales[OrderDate], Date[Date], BOTH))
  4. CALCULATE([Sales Amount], TREATAS(VALUES(Date[Date]), Sales[ShipDate]))
Explanation: When your model has both active and inactive relationships between two tables, the key function to understand is USERELATIONSHIP. This DAX function temporarily activates an inactive relationship within the context of a CALCULATE call, overriding the default active relationship for that specific measure evaluation. In this scenario, the active relationship flows through Sales[OrderDate], meaning [Sales Amount] naturally filters by order date. To make the measure respond to Date table filters via Sales[ShipDate] instead, you need to activate that inactive relationship — which is exactly what B does: CALCULATE([Sales Amount], USERELATIONSHIP(Sales[ShipDate], Date[Date])). This tells DAX to use the ship-date path when evaluating the measure, so any filter on Date[Date] propagates through ShipDate. A is a trap. It calls USERELATIONSHIP with Sales[OrderDate], which is already the active relationship — this changes nothing and still reports by order date, not ship date. C uses CROSSFILTER, which controls the direction of an existing relationship (one-way vs. both directions), not which relationship is active. It doesn't switch the filter path to ship date and would not produce the intended result. D uses TREATAS, which remaps a column's values onto another column virtually. While creative, this bypasses the relationship model entirely rather than properly activating the intended relationship, and it introduces unnecessary complexity where USERELATIONSHIP is the idiomatic solution. A reliable study tip: whenever you see an inactive relationship and a requirement to filter through it, USERELATIONSHIP is almost always the correct tool — not CROSSFILTER or TREATAS.

Question 10

Each product has one value in Product[Discount Rate]. The following measure works on individual product rows but returns blank at totals containing products with different discount rates:

Discounted Sales = [Sales Amount] * (1 - SELECTEDVALUE(Product[Discount Rate]))

The total must be the sum of each product's sales after applying that product's discount rate.

Which revised measure meets the requirement?

  1. [Sales Amount] * (1 - AVERAGE(Product[Discount Rate]))
  2. CALCULATE([Sales Amount] * (1 - SELECTEDVALUE(Product[Discount Rate])))
  3. SUMX(VALUES(Product[ProductKey]), [Sales Amount] * (1 - CALCULATE(MAX(Product[Discount Rate])))) (correct answer)
  4. SUMX(VALUES(Product[ProductKey]), [Sales Amount] * (1 - MAX(Product[Discount Rate])))
Explanation: When a measure needs to compute a row-level calculation and then aggregate across multiple rows, your instinct should be to reach for SUMX — an iterator that loops through a table and evaluates an expression for each row before summing the results. Here, the goal is to apply each product's specific discount rate to its own sales, then total everything up. The correct approach, option C, does exactly this: SUMX(VALUES(Product[ProductKey]), [Sales Amount] * (1 - CALCULATE(MAX(Product[Discount Rate])))). It iterates over each unique product, and within each iteration, CALCULATE forces the filter context to that single product row, making MAX(Product[Discount Rate]) resolve to that product's one discount rate. The results are then summed — giving you the correct weighted total. Option A fails immediately because averaging discount rates across products is mathematically wrong. A product with $1,000 in sales and a 10% discount is not equivalent to one with $10 in sales at 50% — a simple average ignores sales volume entirely. Option B wraps the original expression in CALCULATE, but without an iterator, the measure still evaluates in the full filter context at the total level. SELECTEDVALUE still returns blank when multiple products are present, so the total remains blank. Option D is the subtle trap. It looks nearly identical to C, but omits CALCULATE inside the SUMX loop. Without CALCULATE, the filter context from the iterator isn't properly transitioned into the row context, so MAX(Product[Discount Rate]) may not resolve correctly to the single-product value. Study tip: Whenever you need row-level logic at a summary level in DAX, remember the pattern: SUMX + VALUES + CALCULATE inside the expression. That combination is a reliable foundation for many aggregation problems on the exam.