Exam 70-778 All Questions

View all questions & answers for the 70-778 exam

Exam 70-778 topic 1 question 39 discussion

Actual exam question from Microsoft's 70-778

Question #: 39
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 exactly the same in each question in this series.
Start of repeated scenario.
You have a Microsoft SQL Server database that has the tables shown in the Database Diagram exhibit. (Click the Exhibit button.)

You plan to develop a Power BI model as shown in the Power BI Model exhibit. (Click the Exhibit button.)

You plan to use Power BI to import data from 2013 to 2015.
Product Subcategory[Subcategory] contains NULL values.
End of Repeated Scenario.
You need to create a measure of Sales[SalesAmount] where Product[Color] is Red or Product[Size] is 50.
Which DAX formula should you use?

A.

B.

C.

D.

Show Suggested Answer

Suggested Answer: C
References:
https://msdn.microsoft.com/query-bi/dax/filter-function-dax

by Orl at July 18, 2019, 4:11 p.m.

Comments

Submit Cancel

exam_man20

Highly Voted 5 years, 10 months ago

The Answer is B. The filter function in C does not have a table in its first arg.

upvoted 10 times

...

Orl

Highly Voted 5 years, 10 months ago

It should be the answer B.

upvoted 6 times

SteveKarr

5 years, 10 months ago

C is the correct answer, In calculate you cannot have an expression referring more than one column. You in this case we are required to write an explicit FILTER in the second argument of the CALCULATE.

upvoted 29 times

Orl

5 years, 10 months ago

you're right. I did not see the first answer and take the second one as the A

upvoted 2 times

...

korapavan433

4 years, 7 months ago

Tested it worked. Others don't make confusion here. Before posting commenting here, Please test and post otherwise, people can confuse and chances are more go with the wrong assumption.

upvoted 3 times

...

Xmael

Most Recent 4 years ago

Right ans is B because structure of is FILTER (<table>, <Colonne>) diffrent to FILTERS

upvoted 1 times

...

Raja_G

4 years, 1 month ago

Answer A is also valid now after the March 2021 Update. A & B both correct

upvoted 3 times

...

nemanjaandic

4 years, 3 months ago

Option B is incorrect: Total = CALCULATE(SUM('Table (2)'[Sales Amount]),'Table (2)'[Product Color]="Blue" || 'Table (2)'[Product Size]="A") -- this returns error, only single column can be used in a True/False expression. So answer is C. When using Filter function, we can define two columns in filter.

upvoted 1 times

...

CDL

4 years, 7 months ago

B is correct. Link: https://www.sqlbi.com/articles/filter-arguments-in-calculate/ You can write a filter over two columns using a filter over the entire table that contains both columns. [Sales Red or Contoso - table filter] := CALCULATE ( [Sales Amount], FILTER ( 'Product', 'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso" ) ) Or the other Nested DAX like = SUMX(Filter(table, filter-1, filter2), [Sales Amount])) Example: = SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])

upvoted 1 times

...

CDL

4 years, 7 months ago

B is correct. Link: You can write a filter over two columns using a filter over the entire table that contains both columns. [Sales Red or Contoso - table filter] := CALCULATE ( [Sales Amount], FILTER ( 'Product', 'Product'[Color] = "Red" || 'Product'[Brand] = "Contoso" ) ) Or the other Nested DAX like = SUMX(Filter(table, filter-1, filter2), [Sales Amount])) Example: = SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])

upvoted 1 times

...

sabribrk

4 years, 8 months ago

Answer B

upvoted 2 times

...

cromastro

4 years, 9 months ago

The correct answer is C. The answer is very clear if you read https://www.sqlbi.com/articles/filter-arguments-in-calculate/

upvoted 2 times

...

p_eloy

4 years, 11 months ago

Folks, pay attention! The DAX measure is above from the option. The correct DAX measure is represented by option C like the first measure is above from the A option.

upvoted 4 times

...

Onkar26

4 years, 11 months ago

Correct answer is C. I have tried on my system and its working fine. other are generating error or giving incorrect result.

upvoted 3 times

...

Echicken69

4 years, 12 months ago

I can be either B or C according to the reference link. [Sales Red or Blue] := CALCULATE ( [Sales Amount], Product[Color] = "Red" || Product[Color] = "Blue" ) The syntax above is internally transformed in to the following one, which you can write in an explicit way obtaining the same behavior from the DAX Measure. [Sales Red or Blue] := CALCULATE ( [Sales Amount], FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" || Product[Color] = "Blue" ) )

upvoted 2 times

...

sumitmalik

5 years ago

CORRECT ANSWER IS CALCULATE( SUM( Product_Fact[Amount] ), FILTER( Product_Dim , Product_Dim[Color]="red" || Product_Dim[Size] = 50 ) ) as by raspbeyy I too tried

upvoted 2 times

...

las

5 years, 1 month ago

Please notice that the options A, B, C and D are marked AFTER the answer which makes the correct answer C.

upvoted 5 times

...

Jrestrepo

5 years, 2 months ago

The answer is B. I validated this and if I didn't put the table (Product) before the condition it didn't work. . . .

upvoted 1 times

...

lozqt

5 years, 3 months ago

The syntax for ')' is incorrect. (DAX(CALCULATE(sum(SALES[TotalCost]),PRODUCTS[Color] = "pink" || PRODUCTS[Size] = "M"))) --> what am i told when trying B. For me the correct answer is C because in B you do not state that you want to filter so Power BI won't understand it itself

upvoted 1 times

...

raspberry

5 years, 3 months ago

The question is badly edited so it creates misunderstanding. This is correct, tested, answer: CALCULATE( SUM( Product_Fact[Amount] ), FILTER( Product_Dim , Product_Dim[Color]="red" || Product_Dim[Size] = 50 ) )

upvoted 6 times

...

Load full discussion...