NEW QUESTION 1
You have a table named Sales. Sales contains the following columns.

You need to add a column that shows the first three letters of the day of the week.
How should you complete the DAX formula? 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
FORMAT([Date], "DDD")
http://community.powerbi.com/t5/Desktop/Any-way-of-converting-numeric-month-amp-weeks-to-their/m-p/451

NEW QUESTION 2
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result these questions will not appear in the review screen.
You have an Excel workbook that contains a table named Table1. A sample of the data in Table1 is shown in the following table.

You need to create a PivotTable in PowerPivot as shown in the exhibit.

Solution: You create a measure named Products the uses the CONCATENATEX DAX function. You add a PivotTable. You drag Products to the Rows field. You drag Price to the Values field.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 3
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, white others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a query named Query1 that retrieves the user information from two Excel files. One of the Excel files does not contain location information. A sample of the data retrieved t]y the query is shown in the following table.

You need to ensure that values in UserName are unique. The solution must ensure that the locations are retained. A sample of desired output is shown in the following table.

Solution: You select the-UserName and Location columns, and then you click Remove Duplicates. Does this meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 4
You have a measure that is used by a KPI.
You need to display the output of the measure in a cell in your workbook. Which Excel function should you use?

• A. CUBEVALUE
• B. VLOOKUP
• C. CUBESET
• D. LOOKUP

Answer: A

Explanation:
https://support.office.com/en-us/article/cubevalue-function-8733da24-26d1-4e34-9b3a-84a8f00dcbe0 https://powerpivotpro.com/2010/06/using-excel-cube-functions-with-powerpivot/

NEW QUESTION 5
From a workbook query, you import a table that has the following data.

You need to configure the table to appear as shown in the following table.

What should you do?

• A. From the Format menu, click Trim.
• B. From the Format menu, click Clean.
• C. From the Split Column menu, click By Delimiter.
• D. From the Extract menu, click Last Characters.

Answer: A

NEW QUESTION 6
You need to create a PivotChart as shown in the exhibit. (Click the Exhibit button.) Exhibit:

Which field should you use for each area? To answer, drag the appropriate fields to the correct areas. Each field 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

Legend: BrandName Axis: MonthName

NEW QUESTION 7
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
Start of repeated scenario.
You are creating reports for a car repair company. You have four datasets in Excel spreadsheets. Four workbook queries load the datasets to a data model. A sample of the data is shown in the Data Sample exhibit. (Click the Exhibit button.)
Data Sample exhibit:

The data model is shown in the Data Model exhibit. (Click the Exhibit button.)

The tables in the model contain the following data:
DailyRepairs has a log of hours and revenue for each day, workshop, and repair type. Every day, a log entry is created for each workshop, even if no hours or revenue are recorded for that day. Total Hours and Total Revenue column.
Workshops have a list of all the workshops and the current and previous workshop managers. The format of the Workshop Manager column is always Firstname Lastname. A value of 1 in the IsLatest column indicates that the workshop manager listed in the record is the current workshop manager.
RepairTypes has a list of all the repair types
Dates has a list of dates from 2015 to 2021
End of repeated scenario.
You need to create a PivotChart that displays the month, the hours of the month, and the hours of the previous month, as shown in the following exhibit.

Which DAX formula should you use for the Total Hours Last Month measure? To answer, drag the appropriate fields 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
IF (ISBLANK[(Total Hours]),BLANK(), CALCULATE([total Hours], DATEADD(tblDates(Date),
-1,MONTH)))

NEW QUESTION 8
You have the PivotTable shown in the following exhibit.

You need to display only rows in the PivotTable in which the sum of LineTotal is greater than 100. What should you do?

• A. From Row Label, configure a Label filter.
• B. Add a slicer for LineTotal and select the values from the slicer.
• C. From Row Label, configure a Value Filter.
• D. Add LineTotal to the Filters area of PivotTable Field
• E. Configure the Filters value.

Answer: B

NEW QUESTION 9
You have a model that contains the following table named Sales.

You have a measure named TotalSales that calculates the sum of LineTotal.
You plan to create a PivotChart to display TotalSales for each category and the percent of total sales for each category as shown in the following exhibit.

How should you complete the DAX formula for the Percent measure? To answer, drag the appropriate fields to the correct areas. Each field 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
DIVIDE(TotalSales , CALCULATE(LineTotal, ALL(TotalSales), 0))

NEW QUESTION 10
You create an Excel workbook named SalesResults.xlsx. You create a workbook query that connects to a Microsoft SQL Server database and loads data to the data model. You create a PivotTable and a PivotChart.
You plan to share SalesResults.xlsx to several users outside of your organization.
You need to ensure that the users can see the PivotTable and the PivotChart when they open the file. The data in the model must be removed.
What should you do?

• A. Run the Document Inspector.
• B. Save the workbook as an Excel Binary Workbook (.xlsb).
• C. From Query Editor, open the Data Source Settings and delete the credentials.
• D. Modify the source of the query.

Answer: C

NEW QUESTION 11
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
Start of repeated scenario
You have six workbook queries that each extracts a table from a Microsoft Azure SQL database. The tables are loaded to the data model, but the data is not loaded to any worksheets. The data model is shown in the Data Model exhibit.

Your company has 100 product subcategories and more than 10,000 products. End of repeated scenario.
You need to create a simplified view of the workbook for some users. The simplified view must only display data from FactSales, DimProduct, and DimDate.
What should you do in the data model?

• A. Click Hide from Client Tolls for all the tables except FactSales, DimProduct, and DimDate.
• B. Create a new perspective.
• C. Modify the Table Behavior settings for FactSales, DimProduct, and DimDate.
• D. Add the columns from FactSales, DimProduct, and DimDate to the Default Field Set.

Answer: A

NEW QUESTION 12
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
Start of repeated scenario.
You have six workbook queries that each extracts a table from a Microsoft Azure SQL database. The tables are loaded to the data model, but the data is not loaded to any worksheets. The data model is shown in the Data Model exhibit. (Click the Exhibit button.)
Exhibit:

Your company has 100 product subcategories and more than 10,000 products. End of repeated scenario.
You plan to use the DAX time intelligence functions of DATEADD and DATESMTD. You need to ensure that the functions return the correct data.
What should you do first?

• A. Delete and recreate the relationship between FactSales and DimDate.
• B. Change the Data Type of FactSales[DateKey].
• C. Mark DimDate as the date table.
• D. Change trie Data Type of DimDate[DateKey].

Answer: C

Explanation:
https://docs.microsoft.com/en-us/sql/analysis-services/lesson-3-mark-as-date-table?view=sql-analysis-services-2

NEW QUESTION 13
You have a workbook query that gets data from a table in a Microsoft Azure SQL database. The table has a column named Phone. The values in Phone are in a format of 999-999-9999. The first three digits of each phone number represent the area code, and the rest of the digits represent the local phone number.
You need to split the Phone column into two columns. The first column must contain the area code, and the second column must contain the local phone number.
How should you complete the query? 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
=Table.AfterDelimeter(#"Changed Type",
Phone,Splitter.SplitTextByEachDelimiter({"-"},QuoteStyle.Csv,false),{"Area Code","Local Phone"})
=Phones.AfterDelimeter(#"Changed Type",
Phone,Splitter.SplitTextByEachDelimiter({"-"},QuoteStyle.Csv,false),{"Area Code","Local Phone"})

NEW QUESTION 14
You have a query that retrieves customers and their locations. You have sample of the data as shown in the following table.

Additional customers and locations are added frequently.
You need to transform the data as shown in the following table.

What should you do?

• A. Select the Locations columns and the select split column by delimite
• B. Use a comma as the delimiter and split into rows.
• C. Select the Customer columns, and then click Unpivot Columns.
• D. Select the Customer columns, and then click Unpivot other Columns.
• E. Select the Location columns and then select split column by delimite
• F. Use a comma as the delimiter and split into columns.

Answer: A

NEW QUESTION 15
You create an Excel workbook named SalesResults.xlsx. You create a workbook query that connects to a Microsoft SQL Server Database and loads data to the data model. You create a PivotTable and PivotChart.
You plane to share SalesResults.slsx to several users outside of your organization.
You need to ensure that the users can see the PivotTable and the PivotChart when they open the file. The data in the model must be removed.
What should you do?

• A. Modify the source of the query.
• B. From Query Editor, open the Data Source Setting and delete the credentials.
• C. Run the Document inspector.
• D. Save the workbook as an Excel Binary Workbook (xlsx)

Answer: A

Explanation:
References:
https://support.office.com/en-us/article/data-source-settings-power-query-9f24a631-f7eb-4729-88dd-6a4921380

NEW QUESTION 16
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Power Pivot model that contains the following tables.

There is a relationship between Products and ProductCategory.
You need to create a hierarchy in Products that contains ProductCategoryName and ProductName.
Solution: You create a measure that uses the ISCROSSFILTERED DAX function Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 17
You have the following data sample.

You need to create a PivotTable that presents the data as shown in the following table.

• A. Create a PivotTabl
• B. Add UnitPrice to the Rows area and add SalesAmount to the Values area.Right-click a cell value for UnitPrice and modify the Group settings.
• C. Create a PivotTabl
• D. Add SalesAmount to the Rows area and add UnitPrice to the Values area.Right-click a cell value for SalesAmount and modify the Group settings.
• E. Create a PivotTabl
• F. Add UnitPrice to the Rows area and add SalesAmount to the Values area.Right-click a cell value for SalesAmount and modify the Field Settings.
• G. Create a PivotTabl
• H. Add SalesAmount to the Rows area and add UnitPrice to the Values area.Right-click a cell value for UnitPrice and modify the Field Settings.

Answer: A

NEW QUESTION 18
Note: This question is part of a series of questions that use the same scenario, For your convenience is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
Start of repeated scenario
You have six workbook queries that each extracts a table from a Microsoft Azure SQL database. The tables are loaded to the data model, but the data is not loaded to any worksheets. The data model is shown in the Data Model exhibit.

Your company has 100 product subcategories and more than 10,000 products. End of repeated scenario.
You need to create a chart as shown in the following exhibit.

Which field should you use for each area? To answer, drag the appropriate fields to the correct areas. Each field 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Axis: Month Year Legend: ChanelName Values: SalesAmount

NEW QUESTION 19
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it As a result, these questions will not appear in the review screen.
You have an Excel workbook that contains a table named Table1. A sample of the data in Table1 is shown in the following table.

You need to create a PivotTable in PowerPivot as shown in the exhibit.

Solution: You create a hierarchy named Products that contains ProductCategory, ProductSubCategory, and ProductName. You add a PivotTable. You drag Products to the Rows field. You drag Price to the Values field.
Does this meet the goal?

• A. Yes
• B. No

Answer: A

Explanation:
Hierarchy Products that contains ProductCategory, ProductSubCategory, and ProductName https://www.tutorialspoint.com/excel_power_pivot/excel_power_pivot_hierarchies.htm

NEW QUESTION 20
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have the following data.

You need to retrieve a list of the unique ProductName entries.
Solution: Create a PivotTable that uses the ProductName field in the Rows area. Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 21
You have two queries named Client and Invoices. A sample of Client is shown in the following table.

A sample of Invoices is shown in the following table.

You need to create a new table that has the following information.

Which join kind should you use?

• A. Inner
• B. Left Outer
• C. Right Anti
• D. Left Anti

Answer: B

Explanation:
https://www.excelguru.ca/blog/2015/12/16/merge-tables-using-outer-joins-in-power-query/

NEW QUESTION 22
You have the data model shown in the Data Model exhibit. (Click the Exhibit button.)

You have the PivotChart shown in the Pivot Chart exhibit. (Click the Exhibit button.)

You need to change the current view of the PivotChart to display ProductCategory only. What should you do?

• A. Double-click a bar in the PivotChart.
• B. Click the - button.
• C. Right-click the PivotChart and click Reset to Match Style
• D. Right-click a bar in the PivotChart and click Expand Entire Field.

Answer: D

NEW QUESTION 23
You have a table that contains data relating to exam candidates and their associated scores.
You need to visualize the exam data by separating the data into quartiles. The visualization must display the mean score and must identify any outliers.
Which type of chart should you use?

• A. line
• B. histogram
• C. pie
• D. box and whisker

Answer: D

Explanation:
https://support.office.com/en-us/article/create-a-box-and-whisker-chart-62f4219f-db4b-4754-aca8-4743f6190f0

NEW QUESTION 24
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
Start of repeated scenario.
You are creating reports for a car repair company. You have four datasets in Excel spreadsheets. Four workbook queries load the datasets to a data model. A sample of the data is shown in the Data Sample exhibit. (Click the Exhibit button.)
Data Sample exhibit:

The data model is shown in the Data Model exhibit. (Click the Exhibit button.)

The tables in the model contain the following data:
DailyRepairs has a log of hours and revenue for each day, workshop, and repair type. Every day, a log entry is created for each workshop, even if no hours or revenue are recorded for that day. Total Hours and Total Revenue column.
Workshops have a list of all the workshops and the current and previous workshop managers. The format of the Workshop Manager column is always Firstname Lastname. A value of 1 in the IsLatest column indicates that the workshop manager listed in the record is the current workshop manager.
RepairTypes has a list of all the repair types
Dates has a list of dates from 2015 to 2021
End of repeated scenario.
You create the column chart shown in the Pivot Chart exhibit. (Click the Exhibit button.)

You need to ensure that the month axis is displayed chronologically. What should you do?

• A. In the model, configure the Sort By Column setting for [Month] as [MonthID].
• B. In the model, configure the Sort By Column setting for (Month) as [Date].
• C. In PivotTable Fields, add [MonthID] to the Legend area.
• D. In PivotTable Fields, add [Date] to the Legend area.

Answer: B

NEW QUESTION 25
......