Microsoft DP-600 - Implementing Analytics Solutions Using Microsoft Fabric

To create a calculation item that changes the context from the selected date to month-to-date (MTD), the appropriate DAX expression involves using the CALCULATE function to alter the filter context and the DATESMTD function to specify the month-to-date context.

The correct completion for the DAX expression would be:

In the first dropdown, select CALCULATE .

In the second dropdown, select SELECTEDMEASURE .

This would create a DAX expression in the form:

CALCULATE(

SELECTEDMEASURE(),

DATESMTD( ' Date ' [DateColumn])

)

For the HighestSellingPrice, you should use the GREATEST function to find the highest value from the given price columns. However, T-SQL does not have a GREATEST function as found in some other SQL dialects, so you would typically use a CASE statement or an IIF statement with nested MAX functions. Since neither of those are provided in the options, you should select MAX as a placeholder to indicate the function that would be used to find the highest value if combining multiple MAX functions or a similar logic was available.

For the TradePrice, you should use the COALESCE function, which returns the first non-null value in a list. The COALESCE function is the correct choice as it will return AgentPrice if it ' s not null; if AgentPrice is null, it will check WholesalePrice , and if that is also null, it will return ListPrice .

The complete code with the correct SQL functions would look like this:

SELECT ProductID,

MAX(ListPrice, WholesalePrice, AgentPrice) AS HighestSellingPrice, -- MAX is used as a placeholder

COALESCE(AgentPrice, WholesalePrice, ListPrice) AS TradePrice

FROM Sales.Products

Select MAX for HighestSellingPrice and COALESCE for TradePrice in the answer area.

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.

To prevent new tables added to Lakehouse1 from being automatically added to the default semantic model, you should configure the semantic model settings. There should be an option within the settings of the semantic model to include or exclude new tables by default. By adjusting these settings, you can control the automatic inclusion of new tables.

The two languages you can use to perform model scoring in a Fabric notebook using the predict function are Spark SQL (option C) and PySpark (option D). These are both part of the Apache Spark ecosystem and are supported for machine learning tasks in a Fabric environment. References = You can find more information about model scoring and supported languages in the context of Fabric notebooks in the official documentation on Azure Synapse Analytics .

The relationship between OrderItem and Product must be based on: Both the CompanyID and the ProductID columns

The Company entity must be: Denormalized into the Customer and Product entities

In a dimensional model, the relationships are typically based on foreign key constraints between the fact table (OrderItem) and dimension tables (Product, Customer, Date). Since CompanyID is present in both the OrderItem and Product tables, it acts as a foreign key in the relationship. Similarly, ProductID is a foreign key that relates these two tables. To enable analysis by date, product, and customer, the Company entity would need to be denormalized into the Customer and Product entities to ensure that the relevant company information is available within those dimensions for querying and reporting purposes.

References =

Dimensional modeling

Star schema design

We are analyzing the query:

SELECT e.[WWI Employee ID],

e.Employee,

e.[Preferred Name],

gdr.[WWI Employee ID] AS [Direct Report ID],

gdr.Employee AS [Direct Report]

FROM Dimension.Employee AS e

OUTER APPLY Dimension.GetDirectReports(e.[Employee Key]) AS gdr;

Key concepts:

APPLY operator : Used to join a table with a table-valued function .

OUTER APPLY ensures all rows from the left (Employee) are returned, even if the function returns no rows (similar to LEFT JOIN).

Table-valued function (TVF) : Returns a set of rows (unlike scalar functions, which return a single value).

The function executes once per row from the left table, not once for the whole query.

Evaluating each statement:

" Dimension.GetDirectReports is a scalar T-SQL function. "

No → It must be a table-valued function , because OUTER APPLY requires a table expression.

" The Dimension.GetDirectReports function will run only once when the query runs. "

No → With APPLY , the function runs for each row of Dimension.Employee .

" The output rows will include at least one row for each row in the Dimension.Employee table. "

Yes → Because of OUTER APPLY , every employee row is included, even if there are no direct reports (in that case, the columns from gdr will be NULL).

Final Answer:

Scalar function → No

Runs once per query → No

At least one row per Employee → Yes

When using XMLA endpoint for deployment or processing , the semantic model storage format impacts deployment times.

Small storage format allows faster metadata operations and shorter deployment times.

Large semantic model storage format is only needed for extremely large models ( > 10 GB dataset limit), and it increases deployment overhead.

The other options (B: service editing, C: cache for shortcuts) are unrelated to XMLA endpoint deployment performance.