Microsoft Microsoft Certifications DP-600 Questions & Answers

• Question 51:

  You need to resolve the issue with the pricing group classification.

  How should you complete the T-SQL statement? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  You should use CREATE VIEW to make the pricing group logic available for TSQL 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.

• Question 52:

  You need to create a DAX measure to calculate the average overall satisfaction score.

  How should you complete the DAX code? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  The measure should use the AVERAGE function to calculate the average value.

  It should reference the Response Value column from the 'Survey' table.

  The 'Number of months' should be used to define the period for the average calculation.

  To calculate the average overall satisfaction score using DAX, you would need to use the AVERAGE function on the response values related to satisfaction questions. The DATESINPERIOD function will help in calculating the rolling average

  over the last 12 months.

• Question 53:

  You are creating a dataflow in Fabric to ingest data from an Azure SQL database by using a T-SQL statement.

  You need to ensure that any foldable Power Query transformation steps are processed by the Microsoft SQL Server engine.

  How should you complete the code? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

  Select and Place:

  

  Correct Answer:

  

  You should complete the code as follows: Table NativeQuery EnableFolding

  In Power Query, using Table before the SQL statement ensures that the result of the SQL query is treated as a table. NativeQuery allows a native database query to be passed through from Power Query to the source database. The EnableFolding option ensures that any subsequent transformations that can be folded will be sent back and executed at the source database (Microsoft SQL Server engine in this case).

• Question 54:

  You have a Fabric tenant that contains a semantic model. The model contains data about retail stores.

  You need to write a DAX query that will be executed by using the XMLA endpoint The query must return a table of stores that have opened since December 1,2023.

  How should you complete the DAX expression? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view

  content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  Correct Answer:

  

  The correct order for the DAX expression would be:

  DEFINE VAR _SalesSince = DATE ( 2023, 12, 01 )

  EVALUATE

  FILTER (

  SUMMARIZE ( Store, Store[Name], Store[OpenDate] ),

  Store[OpenDate] >= _SalesSince )

  In this DAX query, you're defining a variable _SalesSince to hold the date from which you want to filter the stores. EVALUATE starts the definition of the query. The FILTER function is used to return a table that filters another table or

  expression. SUMMARIZE creates a summary table for the stores, including the Store[Name] and Store[OpenDate] columns, and the filter expression Store[OpenDate] >= _SalesSince ensures only stores opened on or after December 1,

  2023, are included in the results.

  References =

  DAX FILTER Function DAX SUMMARIZE Function

• Question 55:

  You have a Fabric tenant that contains a lakehouse named Lakehouse1

  Readings from 100 loT devices are appended to a Delta table in Lakehouse1. Each set of readings is approximately 25 KB. Approximately 10 GB of data is received daily.

  All the table and SparkSession settings are set to the default.

  You discover that queries are slow to execute. In addition, the lakehouse storage contains data and log files that are no longer used.

  You need to remove the files that are no longer used and combine small files into larger files with a target size of 1 GB per file.

  What should you do? To answer, drag the appropriate actions to the correct requirements.

  Each action may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  Correct Answer:

  

  Remove the files: Run the VACUUM command on a schedule.

  Combine the files: Set the optimizeWrite table setting. or Run the OPTIMIZE command on a schedule.

  To remove files that are no longer used, the VACUUM command is used in Delta Lake to clean up invalid files from a table. To combine smaller files into larger ones, you can either set the optimizeWrite setting to combine files during write

  operations or use the OPTIMIZE command, which is a Delta Lake operation used to compact small files into larger ones.