Online Access Free 070-779 Exam Questions

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 100
• B.the Total Sales Last Year measure
• C.an absolute value of 0
• D.the Total Sales Current Year measure

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.No
• B.Yes

You have a table in a Microsoft SQL Server database that has more than 5 columns. A sample of the data and some of the columns are shown in the following table.

The table contains more than two million rows. You have 100 clients and 10 products.
You need to load the data to Excel. The solution must minimize the amount of memory used by the model.
What should you do?

• A.Move the database to a Microsoft Azure SQL database. Load the table to the data model.
• B.Load the data to the data model as three tables named Clients, Orders, and Products. Ensure that each table has only the relevant columns. Remove duplicate rows from Clients and Products.
• C.Load the data to three worksheets named Clients, Orders, and Products. Ensure that each worksheet has only the relevant columns. Remove duplicate rows from Clients and Products.
• D.Load the data to one worksheet.

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

The data model is shown in the Data Model exhibit.

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 are two measures defined in DailyRepairs. Total Hours sums the Hours column, and Total Revenue sums the Revenue column.
Workshops has 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 alt the repair types.
Dates has a list of dates from 2015 to 2018.
End of repeated scenario.
You create a measure named Average Revenue Per Hour that calculates the average revenue per hour.
You need to populate a cell in a worksheet to display the Average Revenue Per Hour where Repair Type is Engine.
Which Excel formula should you use?

• A.Option A
• B.Option B
• C.Option C
• D.Option D

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: Open the Advanced Filter dialog box, select Filter the list, in-place, and then select Unique records only.
Does this meet the goal?

• A.No
• B.Yes