Microsoft Microsoft Certified: Azure Enterprise Data Analyst Associate DP-500 Questions & Answers

• Question 21:

  HOTSPOT

  You are building a Power Bl dataset that contains a table named Calendar. Calendar contains the following calculated column.

  pfflag = IF('Calendar'[Date] < TOOAYQ, "Past", "Future")

  You need to create a measure that will perform a fiscal prior year-to-date calculation that meets the following requirements:

  Returns the fiscal prior year-to-date value for [sales Amount]

  Uses a fiscal year end of June 30

  Produces no result for dates in the future

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 22:

  HOTSPOT

  You have the Power BI workspaces shown in the following exhibit.

  

  Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 23:

  HOTSPOT

  You are configuring an aggregation table as shown in the following exhibit.

  

  The detail table is named FactSales and the aggregation table is named FactSales(Agg).

  You need to aggregate SalesAmount for each store.

  Which type of summarization should you use for SalesAmount and StoreKey? To answer, select the appropriate options in the answer area,

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 24:

  HOTSPOT

  You use Advanced Editor in Power Query Editor to edit a query that references two tables named Sales and Commission. A sample of the data in the Sales table is shown in the following table.

  

  A sample of the data in the Commission table is shown in the following table.

  

  You need to merge the tables by using Power Query Editor without losing any rows in the Sales table. How should you complete the query? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 25:

  HOTSPOT

  You need to recommend an automated solution to monitor Power Bl user activity. The solution must meet the following requirements:

  Security admins must identify when users export reports from Power Bl within five days of a new sensitivity label being applied to the artifacts in Power Bl.

  Power Bl admins must identify updates or changes to the Power Bl capacity.

  The principle of least privilege must be used.

  Which log should you include in the recommendation for each group? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 26:

  HOTSPOT

  You have an Azure Data Lake Storage Gen 2 container that stores more than 300,000 files representing hourly telemetry data. The data is organized in folders by the year, month, and day according to when the telemetry was captured.

  You have the following query in Power Query Editor.

  

  For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point

  Hot Area:

  

  Correct Answer:

  

• Question 27:

  HOTSPOT

  You have the following code in an Azure Synapse notebook.

  

  Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the code. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 28:

  HOTSPOT

  You are creating a Power Bl Desktop report.

  You add a Python visual to the report page.

  You plan to create a scatter chart to visualize the data.

  You add Python code to the Python script editor.

  You need to create the scatter chart.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 29:

  DRAG DROP

  You manage a Power BI dataset that queries a fact table named SalesDetails. SalesDetails contains three date columns named OrderDate, CreatedOnDate, and ModifiedDate.

  You need to implement an incremental refresh of SalesDetails. The solution must ensure that OrderDate starts on or after the beginning of the prior year.

  Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  Correct Answer:

  

  Explanation: Graphical user

  interface, text, application, chat or text message Step 1: Create RangeStart and RangeEndDateTime parameters. When configuring incremental refresh in Power BI Desktop, you first create two Power Query date/time parameters with the

  reserved, case-sensitive names RangeStart and RangeEnd. These parameters, defined in the Manage Parameters dialog in Power Query Editor are initially used to filter the data loaded into the Power BI Desktop model table to include only

  those rows with a date/time within that period.

  Step 2: Add an applied step that adds a custom date filter OrderDate is Between RangeStart and RangeEnd.

  With RangeStart and RangeEnd parameters defined, you then apply custom Date filters on your table's date column. The filters you apply select a subset of data that will be loaded into the model when you click Apply.

  Step 3: Configure an incremental refresh to archive data that starts two years before the refresh date.

  After filters have been applied and a subset of data has been loaded into the model, you then define an incremental refresh policy for the table. After the model is published to the service, the policy is used by the service to create and manage

  table partitions and perform refresh operations. To define the policy, you will use the Incremental refresh and real-time data dialog box to specify both required settings and optional settings.

  Step 4: Add an applied step that filters OrderDate to the start of the prior year.

• Question 30:

  DRAG DROP

  You have an Azure Synapse Analytics serverless SQL pool.

  You need to return a list of files and the number of rows in each file.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the 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:

  

  Box 1: APPROX_COUNT_DISTINCT

  The APPROX_COUNT_DISTINCT function returns the approximate number of unique non- null values in a group.

  Box 2: OPENROWSET

  OPENROWSET function in Synapse SQL reads the content of the file(s) from a data source. The data source is an Azure storage account and it can be explicitly referenced in the OPENROWSET function or can be dynamically inferred from

  URL of the files that you want to read. The OPENROWSET function can optionally contain a DATA_SOURCE parameter to specify the data source that contains files.

  The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name OPENROWSET. It supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a

  rowset.