exam questions

Exam 70-779 All Questions

View all questions & answers for the 70-779 exam

Exam 70-779 topic 1 question 21 discussion

Actual exam question from Microsoft's 70-779
Question #: 21
Topic #: 1
[All 70-779 Questions]

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 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. =CUBEMEMBER("ThisWorkbookDataModel", "[DailyRepairs]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[Dimensions]. [Repair Type]. [Engine]"))
  • B. =CUBEVALUE("ThisWorkbookDataModel", "[Measures]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[Dimensions]. [Repair Type]. [Engine]"))
  • C. =CUBEMEMBER("ThisWorkbookDataModel", "[DailyRepairs]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[RepairTypes]. [Repair Type]. [Engine]"))
  • D. =CUBEVALUE("ThisWorkbookDataModel", "[Measures]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[RepairTypes]. [Repair Type]. [Engine]"))
Show Suggested Answer Hide Answer
Suggested Answer: B 🗳️
References:
https://support.office.com/en-us/article/cubevalue-function-8733da24-26d1-4e34-9b3a-84a8f00dcbe0 https://www.tutorialspoint.com/advanced_excel_functions/advanced_excel_cube_cubemember_function.htm

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
tho
Highly Voted 5 years, 5 months ago
The answer should be D.
upvoted 26 times
hfgirona
4 years, 5 months ago
The DAX formula from D works but when you can not choose .[engine], only .[All]. This returns only the grand total not for [Engine].
upvoted 1 times
...
...
mohroshdy
Highly Voted 5 years, 5 months ago
The correct answer is D
upvoted 7 times
...
P_S09
Most Recent 4 years, 5 months ago
dimensions is not a table name in the diagram view, when calling cube member you call the table, column, value
upvoted 1 times
...
CibrevN
4 years, 11 months ago
B is correct
upvoted 1 times
DataDoc
4 years, 11 months ago
If B is correct, where does [Dimensions] come from?
upvoted 3 times
...
...
Vermonster
5 years, 2 months ago
Agree - should be D but the formula is missing the & before the value of [Engine]: =CUBEVALUE("ThisWorkbookDataModel", "[Measures]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[RepairTypes]. [Repair Type]. [Engine]")). Should be =CUBEVALUE("ThisWorkbookDataModel", "[Measures]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[RepairTypes]. [Repair Type]. &[Engine]"))
upvoted 1 times
...
mcsetc
5 years, 2 months ago
Should be D. =CUBEVALUE("ThisWorkbookDataModel", "[Measures]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[RepairTypes]. [Repair Type]. &[Engine]"))
upvoted 1 times
...
SN22
5 years, 3 months ago
Correct answer is D
upvoted 5 times
...
Ello
5 years, 3 months ago
It should be [RepairTypes] instead of [Dimensions], so answer D
upvoted 5 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
Loading ...
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago