- Which replacement measure should you use?
Answer: SUMX(Sales, Sales[Quantity] * RELATED(Product[List Price]))
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.
- What best explains the different results?
Answer: A measure reference receives an implicit CALCULATE, which transitions the customer row context into filter context.
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.
- How should you modify the measure?
Answer: Use CALCULATE([Sales Amount], KEEPFILTERS(Product[Color] = "Blue")).
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.
- Which revision most directly fixes the measure?
Answer: CALCULATE([Sales Amount], DATEADD(Date[Date], -1, YEAR))
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.
- Which denominator should you use to ignore every product-table filter while retaining date and region filters?
Answer: CALCULATE([Sales Amount], ALL(Product))
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.
- Which expression should replace
<denominator>? Answer: CALCULATE([Sales Amount], ALLSELECTED(Product[Category]))
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.
- Which change most directly restores the original evaluation behavior?
Answer: Wrap SUM(Sales[Amount]) in CALCULATE so the customer row context becomes filter context.
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.
- Which measure should you create?
Answer: CALCULATE([Sales Amount], USERELATIONSHIP(Sales[ShipDate], Date[Date]))
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.
- Which condition should replace
HASONEVALUE(Product[Subcategory])? Answer: ISINSCOPE(Product[Subcategory])
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.
- Which revised measure meets the requirement?
Answer: SUMX(VALUES(Product[ProductKey]), [Sales Amount] * (1 - CALCULATE(MAX(Product[Discount Rate]))))
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.
