Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Easy
Question 35
At SkyMetrics Corp., your manager asks you to calculate the total quantity sold for a specific product category, "Electronics."
The Sales table is linked to the Products table, which contains:
ProductIDCategory
The Sales table contains:
ProductIDQuantity
What DAX formula should you use to create this measure?
-
A
Total Electronics Quantity = CALCULATE(SUM(Sales[Quantity]), Products[Category] = "Electronics")
-
B
Total Electronics Quantity = FILTER(SUM(Sales[Quantity]), Products[Category] = "Electronics")
-
C
Total Electronics Quantity = SUMX(FILTER(Products, Products[Category] = "Electronics"), Sales[Quantity])
-
D
Total Electronics Quantity = SUM(Sales[Quantity])
Reveal correct answer
Correct answer: A
Explanation
Create the DAX Measure Step-by-Step
Use CALCULATE with a Filter
To calculate the total quantity sold for the "Electronics" category at SkyMetrics Corp, use the DAX formula: Total Electronics Quantity = CALCULATE(SUM(Sales[Quantity]), Products[Category] = "Electronics"). In Power BI Desktop, go to the Modeling tab, select "New Measure," and enter this formula. CALCULATE adjusts the filter context to include only rows where Products[Category] is "Electronics," then SUM aggregates the Sales[Quantity] column, leveraging the ProductID relationship between tables. The Microsoft DAX CALCULATE function reference explains that "CALCULATE modifies the context to apply filters before evaluating an expression," making it efficient and precise for this task.
Why Not Other Approaches?
Simple SUM
Total Electronics Quantity = SUM(Sales[Quantity]) totals all quantities across categories, ignoring the "Electronics" restriction. The SUM function documentation confirms it "aggregates without inherent filtering."
SUMX with FILTER
Total Electronics Quantity = SUMX(FILTER(Products, Products[Category] = "Electronics"), Sales[Quantity]) works but is less efficient, iterating row-by-row over the filtered Products table. The SUMX function documentation notes it’s for "complex row-level calculations," not simple aggregates.
FILTER with SUM
Total Electronics Quantity = FILTER(SUM(Sales[Quantity]), Products[Category] = "Electronics") is invalid syntax. FILTER returns a table, not a scalar value for SUM. The FILTER function documentation states it’s for "table manipulation, not direct aggregation."
Additional Considerations
Relationship: Ensure a single-directional, active relationship between Sales[ProductID] and Products[ProductID], per the relationships documentation.
Validation: Add the measure to a card visual and filter by Electronics to confirm.
Final Answer
Use the DAX formula Total Electronics Quantity = CALCULATE(SUM(Sales[Quantity]), Products[Category] = "Electronics") to calculate the total quantity sold for the "Electronics" category at SkyMetrics Corp, leveraging the table relationship and filter context effectively.
A.
CALCULATE changes the filter context.
The condition Products[Category] = "Electronics" filters the Products table.
Since Products is related to Sales via ProductID, this filter flows down to the Sales table.
SUM(Sales[Quantity]) then operates only on rows where the category is "Electronics".
B.
Invalid use of FILTER. It does not wrap around SUM. FILTER must be used inside functions like CALCULATE or SUMX.
C.
This assumes Products is being iterated over, and would work only if the relationship is properly set, but it's not as readable or direct as using CALCULATE.
D.
This calculates total quantity for all products — no category filter is applied.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
