Implementing Analytics Solutions Using Microsoft Fabric DP-600 Exam Questions with Experts Answers Updated Recently

To identify the slowest DAX query and analyze the time it spends in the formula engine compared to the storage engine, you should perform the following actions in sequence:

From Performance analyzer, capture a recording.

View the Server Timings tab.

Enable Query Timings and Server Timings. Run the query.

View the Query Timings tab.

Sort the Duration (ms) column in descending order by DAX query time.

We are analyzing this KQL query:

Sales

| where Status != "Cancelled"

| where OrderDate >= ago(30d)

| summarize TotalSales = sum(SalesAmount) by ProductCategory

| where TotalSales > 0

Statement 1:

"The query excludes sales that have a Status of Cancelled."

Yes → where Status != "Cancelled" filters them out.

Statement 2:

"The query calculates the total sales of each product category for the last 30 days."

Yes → where OrderDate >= ago(30d) ensures only last 30 days.

summarize TotalSales = sum(SalesAmount) by ProductCategory groups by category.

Statement 3:

"The query includes product categories that have had zero sales during the last 30 days."

No → The final filter where TotalSales > 0 excludes categories with zero sales.

Final Answer:

Excludes Cancelled sales → Yes

Calculates total sales by product category for last 30 days → Yes

Includes product categories with zero sales → No

Comprehensive Detailed Explanation

We need to write a query that summarizes order quantities by year and product, and also include the yearly total for all products in each row.

Step 1: What the query needs

Extract the year from SalesOrderDetail.ModifiedDate.

Join with the Product table to get the product name.

Aggregate with SUM(OrderQty).

Return grouped data by year and product, with an additional subtotal row per year (all products combined).

Step 2: Evaluate the SELECT Clause

We must extract the year using:

YEAR(so.ModifiedDate) AS OrderDate

This converts the ModifiedDate column into a year value for grouping.

Step 3: Evaluate the GROUP BY options

CUBE(YEAR, P.Name) → Produces all combinations of year totals, product totals, and grand totals. Too many combinations, not required.

GROUPING SETS → Could achieve the result but requires explicitly listing the sets. Less direct.

ROLLUP(YEAR, P.Name) → Produces grouping by (Year, Product) and then a subtotal per Year. Exactly what is required.

YEAR only → Would group only by year, losing per-product breakdown.

Correct: ROLLUP(YEAR(so.ModifiedDate), P.Name)

Step 4: Completed Query

SELECT

YEAR(so.ModifiedDate) AS OrderDate,

p.Name,

SUM(so.OrderQty) AS OrderQty

FROM dbo.SalesOrderDetail so

INNER JOIN dbo.Product p

ON p.ProductID = so.ProductID

GROUP BY ROLLUP(YEAR(so.ModifiedDate), p.Name);

Why This Works

YEAR(so.ModifiedDate) extracts year for grouping.

ROLLUP(YEAR, P.Name) provides both product-level totals and yearly subtotals.

Ensures the requirement: “include the yearly sum of order quantities for all the products in each row.”

References

GROUP BY ROLLUP in T-SQL

Aggregate functions in Microsoft Fabric warehouses