Microsoft 70-779 Faq 2021

NEW QUESTION 1
You have multiple workbook queries that load data from tables in Microsoft Azure SQL Database to a Power Pivot data model.
You discover that new rows were added to the tables in Azure SQL Database. You need to ensure that the workbook has the new data.
Why should you do?

• A. Select a cell in the worksheet and pressF5.
• B. From the data tab, click Refresh All.
• C. Close and open the workbook.
• D. From the Power Pivot tab, click Update All.

Answer: B

Explanation:
Refresh data from a Microsoft Query, the Data Connection Wizard, or web query
Click any cell in the range or table that contains the link to the external data.
On the Data tab, in the Connections group, click Refresh All.

To update only the selected data, click Refresh.
You can also right-click a cell in the range or table, and then click Refresh.
To update all the data connections in the workbook, click Refresh All.
Note: If you have more than one workbook open, you'll need to repeat the operation in each workbook. https://support.office.com/en-us/article/refresh-an-external-data-connection-in-excel-2021-for-windows-152417

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 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 USERELATIONSHIP DAX function.
Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 3
You have a data model that contains a table named SalesOrders has four columns named Orderld, SalesAmount, OrderDate, and Territory.
You plan to create a PivotChart that will display the percentage of SalesAmount for each Territory. You need to create a measure to calculate the percentage of sales of each territory.
How should you complete the DAX formula? To answer, drag the appropriate value to the correct targets. Each value may be used once, more than once, or not at all. You need to drag the split bar between panes or scroll to view content.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
SUM([SalesAmount) / ALL(SalesOrders) /(FILTER , ALL(SalesOrders))

NEW QUESTION 4
You create a new workbook and add a table to a data model. The data is shown in the following table.

You need to create a visualization as shown in the following exhibit.

Which three 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Create a Pivot Table. Create a measure.
Create a Power View Report

NEW QUESTION 5
You have a query that retrieves customers and their locations. You have a 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 then select Split Column by Delimite
• B. Use a comma as the delimiter and split into rows.
• C. Select the Locations columns and then select Split Column by Delimite
• D. Use a comma as the delimiter and split into columns.
• E. Select the Customer columns, and then click Unpivot Columns.
• F. Select the Customer columns, and then click Unpivot Other Columns.

Answer: A

NEW QUESTION 6
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 7
You have a workbook query that loads data from a table in a Microsoft Azure SQL database. The table has a column named LineTotal. The following is a sample of the data in LineTotal:
• 40
• 1
• 999
• 7658
• 883432
You need to ensure that when you load the data to the model, LineTotal is set as currency. What should you do from Query Editor?

• A. Configure the Data Type
• B. Round the column.
• C. Split the column by characters.
• D. Split the column by delimiter.

Answer: A

NEW QUESTION 8
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.
Your company has sales offices in several cities.
You create a table that represents the amount of sales in each city by month as shown in the exhibit.

You need to ensure that all values lower than 250 display a red icon. The solution must ensure that all values greater than 500 display a green icon.
Solution: You modify the conditional formatting rule, and then set a new value for the yellow icon. Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 9
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 10
You have a data model in Excel.
You export the data and the data model into a dataset in the Microsoft Power BI service. What can you use to modify the data model that is published to the Power BI service?

• A. Microsoft Power BI Desktop
• B. Excel and the Microsoft Power BI service
• C. Excel only
• D. the Microsoft Power BI service only

Answer: A

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 worksheet. The data model is shown in the Data Model exhibit. (Click the Exhibit button.)
Your company has 100 product subcategories and more than 10,000 products.

End of repeated scenario.
You need to create a measure named [Sales Monthly RT] that calculates a running total of [Sales] for each date within a month as shown in the following exhibit.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
SUM([Sales],DATESMTD(DimDate[Date])) CALCULATE(SUM([Sales],DATESMTD(DimDate[Date])))

NEW QUESTION 12
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 13
You have a model that contains data relating to corporate profits. The model contains a measure named Profit.
You need to create a PivotTable to display the Profit measure in three different formats by using the Show Value As feature. The PivotTabte must produce the results shown in the following table.

How should you configure the Show Value As feature for % Profit of Annual Total and % Profit of Grand Total? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
% Profit of Annual Total: % of Parent Total
% Profit of Grand Total: % of Column Total
https://support.office.com/en-us/article/show-different-calculations-in-pivottable-value-fields-014d2777-ba

NEW QUESTION 14
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 15
You have the following table.

You plan to use [Month Name] as the axis in a PivotChart.
You need to ensure that whenever [Month Name] is used in a chart, the months are displayed chronologically be default.
What should you do?

• A. Sort the [Month Name] column by [Month Name].
• B. Change the Data Type of [Month Name] to Date.
• C. Sort the [Month Name] column by [Month Name].
• D. Add a calculated column named [ID] that use the [Month Name] & [Month Number] DAX formula

Answer: D

Explanation:
References:
https://gasperkamensek.wordpress.com/2013/04/16/sorting-months-chronologically-and-not-alphabetically-in-a-

NEW QUESTION 16
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 17
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 18
You need to create a PivotChart that has a filter as shown in the following exhibit.

What should you do first?

• A. From the model, create a measure.
• B. From Query Editor, create a function.
• C. From Query Editor, create a parameter.
• D. From the model, create a hierarchy.

Answer: A

Explanation:
References:
https://support.office.com/en-us/article/measures-in-power-pivot-86484821-a324-4da3-803b-82fd2e5033f4

NEW QUESTION 19
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 20
You have an Excel workbook that contains a table named Sales. You add Sales to the Power Pivot model.
You need to set a column named TransactionID as the row identifier for the Sales table. What should you do?

• A. From Power Pivot, modify the Table Behavior setting.
• B. From Query Editor, add an index column.
• C. From Query Editor, modify the Data Type.
• D. From Power Pivot, modify the Default Field Set.

Answer: A

Explanation:
https://msdn.microsoft.com/en-us/library/hh560542(v=sql.110).aspx
In the Data View of your PowerPivot Window, click the PowerPivot Window: Advanced Tab.
Click the table tab at the bottom of the window to select the table for which you are configuring
properties.
In Reporting Properties, click Table Behavior.
Set the Row Identifier, and then proceed to specify other properties in this dialog.
Opening the Table Behavior dialog box https://ksdconsultancy.blog/2015/10/08/set-table-behaviour-in-powerpivot/

NEW QUESTION 21
You have a measure named SalesGrowth that calculates the percent of sales growth. The measure uses the following formula.
([Total Sales Current Year] - [Total Sales Last Year]) / [Total Sales Last Year]
Total Sales Current Year is a measure that calculates the sales from the current calendar year. Total Sales Last Year is a measure that calculates the sales from the previous calendar year.
You need to create a KPI that displays a red icon when the sales growth is less than last year. What should you use to define the target value?

• A. an absolute value of 0
• B. the Total Sales Current Year measure
• C. an absolute value of 100
• D. the Total Sales Last Year measure

Answer: D

NEW QUESTION 22
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 query named Queryl 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 by 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 Keep Duplicates. Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 23
You install Microsoft Power BI Publisher for Excel.
You need to use Excel to connect and analyze Power BI data.
To which two types of Power BI data can you connect? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

• A. apps
• B. datasets
• C. reports
• D. dashboard

Answer: BC

Explanation:
Analyze in Excel is very useful for datasets and reports that connect to Analysis Services
Tabular or Multidimensional databases, or from Power BI Desktop files or Excel workbooks with data mo that have model measures created using Data Analysis Expressions (DAX).
https://docs.microsoft.com/en-us/power-bi/service-analyze-in-excel

NEW QUESTION 24
You need to create a combo chart to display the count of orders by month and profit by month as shown in the exhibit. (Click the Exhibit tab.)

How should you configure the combo chart? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Order Count:Stacked columnNo Profit:LineYes
http://www.excel-easy.com/examples/combination-chart.html

NEW QUESTION 25
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 calculated column that uses the RELATED DAX function
Does this meet the goal?

• A. Yes
• B. No

Answer: A

Explanation:

References:
https://www.mssqltips.com/sqlservertip/2900/creating-hierarchies-in-powerpivot-for-excel/ https://msdn.microsoft.com/en-us/library/ee634202.aspx

NEW QUESTION 26
......