Exam 70-778 All Questions

View all questions & answers for the 70-778 exam

Exam 70-778 topic 1 question 97 discussion

Actual exam question from Microsoft's 70-778

Question #: 97
Topic #: 1

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.
You have a Microsoft SQL Server database that contains the following tables.

The following columns contain date information:
✑ Date[Month] in the mmyyyy format
✑ Date[Date_ID] in the ddmmyyyy format
✑ Date[Date_name] in the mm/dd/yyyy format
✑ Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows.
The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables.
You plan to use Power BI Desktop to create an analytics solution for the data.
You plan to create a chart that displays total Order[Order_amount] by Store[Name].
You need to modify the model to ensure that you can create the chart.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Create a relationship between the Order table and the Store table.
B. To the Order table, add a measure that uses the COUNTA('Order'[Order_ID]) DAX formula.
C. To the Order table, add a column that uses the RELATED('Store'[Store_ID]) DAX formula.
D. To the Order table, add a measure that uses the COUNT('Order'[Order_amount]) DAX formula.

Show Suggested Answer

Suggested Answer: AC 🗳️

by shubhamgupta at July 21, 2019, 8:03 a.m.

Comments

Submit Cancel

shubhamgupta

Highly Voted 5 years, 11 months ago

should be A & D with correction .. use SUM function. SUM('Order'[Order_amount]) DAX

upvoted 27 times

RBY10

5 years, 9 months ago

I agree, A & D is obviously the correct answer

upvoted 3 times

Mo2011

4 years, 10 months ago

but you need to use sum not count

upvoted 3 times

...

Arklur

5 years, 8 months ago

I agree too. C) doesn't even makes sense at all, the Order table already has the Store_ID column...

upvoted 3 times

JohnFan

5 years, 6 months ago

If you want to reference a column from a table that is in a one-to-many relationship with the current table, you need to use the RELATED function. For example, you could add a Bill To Customer column to the Sale table with the following formula: Customer = RELATED ( Customer[Customer] ). The relationship must exist before you use RELATED function. Note: RELATED is not USERELATIONSHIP.

upvoted 2 times

JohnFan

5 years, 6 months ago

Therefore, the exact DAXs we should use here are: RELATED('Store'[Name]) and SUM('Order'[Order_amount]), Both of them are not available in all given options. On the other hands, either COUNT or COUNTA functions are too far away from the answers.

upvoted 8 times

GraceZ

4 years, 3 months ago

"The RELATED function requires that a relationship exists between the current table and the table with related information." So you only can use it after you create a relationship between these two tables. https://docs.microsoft.com/en-us/dax/related-function-dax

upvoted 1 times

...

mohroshdy

Highly Voted 5 years, 6 months ago

Guys the answers are correct A&C COUNT or COUNTA will not sum the amounts of the orders, it's very clear

upvoted 15 times

...

Lhouss

Most Recent 4 years, 7 months ago

For me A&C, because other options B &D doens't fit the need. But anyway, in C we retrieve Store_name in Order table and don't agree with this... Bad question :)

upvoted 1 times

...

kyliek7

4 years, 7 months ago

A & C for sure. It's tated that it's only part of the solution so next step could be to create measure that calculates sum of [Order_amount]. Of course it could be straightaway formula which is using related and sum functions, but we don't have this option here.

upvoted 1 times

...

FLA

4 years, 8 months ago

we should add column not a measure.. i agree with C

upvoted 1 times

...

Vi1125

4 years, 8 months ago

Surprised there is so much discussion on this one - I thought this was one as easy one that I got right without even checking the feedback. Answer A is a deffo because the table needs to be related. The question is you "plan to create a chart" need to "modify the model" so to do that you need to create a relationship and then bring the column in (RELATE, answer c)- once that's there in the visual you decide on the output e.g. SUM etc. Easy one I thought.

upvoted 1 times

sjonghe

4 years, 8 months ago

I think you are missing the point. If column [Store_ID] does not exist in 'Order' table then answer 'C' is needed first (before 'A'). But in this example [Store_ID] is already there in 'Order' table, so only 'A' is needed with this starting point. But I think the starting point screenshot is wrong and [Store_ID] column should not already exist in 'Order' table. Then answer 'C' (first) and 'A' are needed.

upvoted 1 times

...

lilyzhang

4 years, 10 months ago

C is to related to STORE_ID column, not store_name column. This is confusing.... If D is sum(), it would be better.

upvoted 1 times

...

Ikay81

4 years, 11 months ago

I just passed my certification exam today 07/31/2020 with a score of 875/700. Thanks to Examtopics and all of you is the discussion.

upvoted 6 times

GIO200011

4 years, 9 months ago

Which one was the correct answer?

upvoted 1 times

...

RJM9000

4 years, 11 months ago

HEY A + C are the solution! With COUNT AND COUNTA you don't get the AMOUNT!!!!

upvoted 4 times

...

J55889

4 years, 11 months ago

Answer: A & C . Please read the question carefully, what they want is store name, not store ID.

upvoted 5 times

...

07071996

5 years, 1 month ago

A and C. Related has used only after a physical relationship exists.

upvoted 2 times

...

elonmusk

5 years, 1 month ago

I'm with A and C. COUNTA does not add order amount up. A and C are just the first two steps before creating measure. The question does not necessarily ask for DAX.

upvoted 3 times

...

IsmMira

5 years, 4 months ago

You should add a column into table to account Stores. The question asks for Order_Aumount. After you add a new column, you can use this in a implict measure to sum Orde_Aumount

upvoted 2 times

...

lozqt

5 years, 4 months ago

"You plan to create a chart that displays total Order[Order_amount] by Store[Name]." - This sentence says nowhere that [Order_amount] must be a sum… right ? Hence "total" can mean (not very clearly I agree…) the total count of order amount which results in "total order ". Taking this into account leaves us with A&D as correct answers as the formula for B counts all data type for the Order_ID column. It is not what is asked. The column concerned here is [Order_Amount].

upvoted 1 times

...

Delta

5 years, 6 months ago

The task of "Create a chart that displays total Order[Order_amount] by Store[Name]." can be done with only performing the first step (option A), in my opinion (C not needed). Given: "The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables." Therefore the first and in my opinion only step is A) to create a relationship between the Order table and the Store table, which is very easy, as both tables contains the StoreID. With this relationship, the requested report is easily setup with a very simple bar-chart with the store-names at the X-as and the order-amount on the Y-axis. Option C is in my opinion therefore not needed. (C. To the Order table, add a column that uses the RELATED('Store'[Store_ID]) DAX formula.) Comments welcome.

upvoted 4 times