ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-778 All Questions

View all questions & answers for the 70-778 exam
Go to Exam

Exam 70-778 topic 1 question 2 discussion

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

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 BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate.
Date contains one column named Date.
The tables have the following relationships:
✑ Sales[DueDate] and Date[Date]
✑ Sales[ShipDate] and Date[Date]
✑ Sales[OrderDate] and Date[Date]
The active relationship is on Sales[DueDate].
You need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.
Solution: You create measures that use the CALCULATE, COUNT, and FILTER DAX functions.
Does this meet the goal?

  • A. Yes
  • B. No
Show Suggested Answer Hide Answer
Suggested Answer: A 🗳️
References:
https://msdn.microsoft.com/en-us/library/ee634966.aspx
https://msdn.microsoft.com/en-us/library/ee634825.aspx
https://msdn.microsoft.com/en-us/library/ee634791.aspx
by AnetaK at July 17, 2019, 9:37 a.m.

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
Submit Cancel
mohroshdy
Highly Voted 5 years, 4 months ago
The answer is Yes This formula will achieve the target: CALCULATE( COUNT(Sales[ShipDate]), FILTER(Sales, NOT(ISBLANK(Sales[ShipDate])) ) ) and same for [OrderDate] but you have to change the columns names only.
upvoted 13 times
raspberry
5 years, 2 months ago
Isn't this filter dummy? Do you mean it should return the whole Sales table without actually filtering anything out of it? If so, it could be done with FILTER (Sales, 1=1)
upvoted 1 times
...
sumitmalik
4 years, 11 months ago
But here ISBLANK is used which is not mentioned in question
upvoted 1 times
...
...
Rathish
Highly Voted 4 years, 11 months ago
I prefer to use USERELATIONSHIP But for this question: yes, given option meets the goal - may not best solution: I tried with both the measures and it is producing same output: Delivery Count REL = CALCULATE(COUNT(Sales[Delivery Date]), USERELATIONSHIP(Sales[Delivery Date], 'Date'[Date])) Delivery Count WREL = CALCULATE(COUNT(Sales[Delivery Date]), FILTER(Sales, NOT(ISBLANK(Sales[Delivery Date]))))
upvoted 8 times
Sam25
4 years, 11 months ago
Perfect Answer by Rathish. I tried both and it works.
upvoted 1 times
...
RajGoy
4 years, 6 months ago
ideally answer will be same, but if you have a date in Sale table, but not in date table then answer will not be same.
upvoted 1 times
...
...
YuanQingTan
Most Recent 4 months ago
Selected Answer: B
there seems to be some misinterpretation of the question when it says count by [orderdate] if this means that we just need to count how many rows with [orderdate] that is not empty then yes it is possible without the userelationship function. however, if count by [orderdate] means count the number of orders that has orderdate for each partition of date, then i think we need to use userelationship function. Consider the sentence count the products by colour. (1)does it mean that we need the total count of the products that has colour? ie: total count= count(red)+count(blue)+count(black), etc. (2)or does it mean that we need the count of product for each colour type? count(red)=10 count(blue)=11 count(black)=12 (2) definitely is more meaningful which i think is what this question actually wants.
upvoted 1 times
...
Jiang54
2 years ago
A Yes Orders by ShipDate = CALCULATE(COUNT(Sales[ShipDate]), userelationship(Sales[ShipDate],Date[Date])
upvoted 1 times
...
KashifIkram
2 years, 1 month ago
A is correct
upvoted 1 times
...
chezleon62
2 years, 2 months ago
Selected Answer: B
USERELATIONSHIP() is the best way
upvoted 1 times
...
Mar_tin
4 years, 4 months ago
It's A) Yes The below given options produce all the same results. I assume USERELATIONSHIP is not needed because you just count the ORDERS simply by counting the Dates ([DueDate], [OrderDate] & [ShipDate] in the 'Sales' Table so no relationsship to 'Date' is needed. -------------------- If you would take e.g. Sales Amount per the above mentioned date columns then USERELATIONSHIP would be needed to create additional "virtual active relationships" 'Date'[Date] 1-* 'Sales'[OrderDate] 'Date'[Date] 1-* 'Sales'[ShipDate] Anyway way using option 1 instead of 3 is a riddle ----------------------- 1)CALCULATE, COUNT & FILTER OrdersByOrderDate = CALCULATE(COUNT(FactInternetSales[OrderDate]), FILTER(FactInternetSales,NOT(ISBLANK(FactInternetSales[OrderDate])))) 2)USERELATIONSHIP CountOrderDateUseRelationship = CALCULATE(COUNT(FactInternetSales[OrderDate]), USERELATIONSHIP(FactInternetSales[OrderDate], 'DimDate'[Date])) 3)JUST COUNT CountOfOrdersByOrderDate = COUNT(FactInternetSales[OrderDate])
upvoted 1 times
...
CDL
4 years, 5 months ago
CALCULATE(COUNT(), FILTER(Sales, Sales[ShipDate] = RELATED(Date[Date] Delivery Count REL = CALCULATE(COUNT(Sales[Delivery Date]), USERELATIONSHIP(Sales[Delivery Date], 'Date'[Date])) Delivery Count WREL = CALCULATE(COUNT(Sales[Delivery Date]), FILTER(Sales, NOT(ISBLANK(Sales[Delivery Date])))) A is right.
upvoted 1 times
...
CDL
4 years, 6 months ago
go with A, Link: https://www.sqlbi.com/blog/marco/2010/02/09/how-to-relate-tables-in-dax-without-using-relationships/#
upvoted 1 times
...
Rahhal
4 years, 8 months ago
I should use USERELATIONSHIP this better
upvoted 1 times
...
kanchanpb
4 years, 9 months ago
The function will not work without using Userelationship
upvoted 1 times
...
aloulouder
4 years, 10 months ago
The both two answers are corrects: ** Mesure1 = CALCULATE(COUNT(fact_InternetSales[ShipDate]),FILTER(fact_InternetSales,NOT((ISBLANK(fact_InternetSales[ShipDate]))))) ** Measure2 = CALCULATE(COUNT(fact_InternetSales[ShipDate]),USERELATIONSHIP(fact_InternetSales[ShipDate],Dim_Date[Date]))
upvoted 1 times
pbia
4 years, 9 months ago
I tried both measure but only the second one worked, I doubt on first measure
upvoted 1 times
...
...
r8d1
4 years, 11 months ago
What CALCULATE does is evaluate a particular expression with a bunch of filters. I think that's what the question is asking. i.e. CALCULATE(Expression, [Filter1], ...)
upvoted 1 times
...
sumitmalik
4 years, 11 months ago
there is no option of USERELATIONSHIP so correct answer should be "NO", Please share your comment
upvoted 1 times
...
cromastro
4 years, 12 months ago
I'm with Anetak. In fact, I think the question alludes to using USERELATIONSHIP because it tells you there are secondary relationships in the model.
upvoted 1 times
...
Sim2312
5 years, 1 month ago
Right Answer is Yes . Refer to https://www.sqlbi.com/blog/marco/2010/02/09/how-to-relate-tables-in-dax-without-using-relationships/
upvoted 3 times
...
JohnFan
5 years, 4 months ago
You have a Power BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate. Date contains one column named Date. The tables have the following relationships: ✑ Sales[DueDate] and Date[Date] ✑ Sales[ShipDate] and Date[Date] ✑ Sales[OrderDate] and Date[Date] The active relationship is on Sales[DueDate]. You need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data. Solution: You create measures that use the CALCULATE, COUNT, and USERELATIONSHIP DAX functions. Does this meet the goal?
upvoted 3 times
...
Load full discussion...
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
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago