Pass the Microsoft Certified: Fabric Analytics Engineer Associate DP-600 Questions and answers with Dumpstech

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])

)

User-defined aggregations (A) and query caching (C) are two features that can help reduce query execution time. User-defined aggregations allow precalculation of large datasets, and query caching stores the results of queries temporarily to speed up future queries. References = Microsoft Power BI documentation on performance optimization offers in-depth knowledge on these features.

For users to create and publish custom Direct Lake semantic models using external tools, following the principle of least privilege, the actions to be included are enabling XMLA Endpoints (A), editing data models in Power BI service (C), and setting XMLA Endpoint to Read-Write in the capacity settings (D). References = More information can be found in the Admin portal of the Power BI service documentation, detailing tenant and capacity settings.

The suggested replacement does not improve performance. COUNTROWS('Order Item') < 0 is always false (row counts cannot be negative), so the NOT will always evaluate to true. This means the filter condition becomes non-selective and would return all customers, not just those with orders. Therefore, it does not meet the optimization goal.

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.

You need to configure OneLake security for two users with the following requirements:

User1: Read all the Spark data

User2: Read all the SQL endpoint data

The available permissions are: Read, ReadAll, and ReadData.

Understanding the permissions:

Read → Grants access to metadata but not the data itself.

ReadData → Grants access to query data through SQL endpoints (SQL-based access).

ReadAll → Grants access to read data across all engines (including Spark and SQL endpoints).

Applying least privilege principle:

User1 (Spark access): Needs to read all the Spark data → Assign ReadAll because Spark requires this permission to access data in OneLake.

User2 (SQL endpoint access): Needs to read SQL endpoint data only → Assign ReadData, as this grants access to SQL endpoints without over-provisioning Spark access.

Final Answer:

User1: ReadAll

User2: ReadData

Step 1 – Creating the new table from a query

To create a table in a SQL warehouse using a query, the correct syntax is:

CREATE TABLE <title_name> AS SELECT ...

So we must select:

CREATE TABLE dbo.POSCustomers AS SELECT

Step 2 – Selecting the source table

Since the data source is the Lakehouse1 table Customer, the fully qualified name is:

lakehouse1.dbo.Customer

Final Answer:

CREATE TABLE dbo.POSCustomers AS SELECT

postalcode,

category

FROM lakehouse1.dbo.Customer;

For ingesting customer data into the data store in the AnalyticsPOC workspace, a dataflow (D) should be recommended. Dataflows are designed within the Power BI service to ingest, cleanse, transform, and load data into the Power BI environment. They allow for the low-code ingestion and transformation of data as needed by Litware's technical requirements. References = You can learn more about dataflows and their use in Power BI environments in Microsoft's Power BI documentation.

Both a dataflow (A) and a Stored procedure activity in a pipeline (B) are capable of creating and populating a date dimension table. A dataflow can perform the transformation needed to create the date dimension, and it aligns with the preference for using low-code tools for data ingestion when possible. A Stored procedure could be written to generate the necessary date dimension data and executed within a pipeline, which also adheres to the technical requirements for the PoC.

You should use CREATE VIEW to make the pricing group logic available for T-SQL queries.

The CASE statement should be used to determine the pricing group based on the list price.

The T-SQL statement should create a view that classifies products into pricing groups based on the list price. The CASE statement is the correct conditional logic to assign each product to the appropriate pricing group. This view will standardize the pricing group logic across different databases and semantic models.