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

Exam DA-100 All Questions

View all questions & answers for the DA-100 exam
Go to Exam

Exam DA-100 topic 2 question 9 discussion

Actual exam question from Microsoft's DA-100
Question #: 9
Topic #: 2
[All DA-100 Questions]

HOTSPOT -
Your company has affiliates who help the company acquire customers.
You build a report for the affiliate managers at the company to assist them in understanding affiliate performance.
The managers request a visual showing the total sales value of the latest 50 transactions for each affiliate. You have a data model that contains the following tables.

The Affiliate table has a one-to-many relationship to the Transactions table based on the AffiliateID column.
You need to develop a measure to support the visual.
How should you complete the DAX expression? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer Hide Answer
Suggested Answer:
Box 1: CALCULATE -
Start with CALCULATE and use a SUMX.
CALCULATE evaluates an expression in a modified filter context.

Box 2: SUMX -
SUMX returns the sum of an expression evaluated for each row in a table.
The following sample creates a measure with the sales of the top 10 sold products.
= SUMX(TOPN(10, SUMMARIZE(Product, [ProductKey], "TotalSales", SUMX(RELATED(InternetSales_USD[SalesAmount_USD]), InternetSales_USD
[SalesAmount_USD]) + SUMX(RELATED(ResellerSales_USD[SalesAmount_USD]), ResellerSales_USD[SalesAmount_USD]))

Box 3: TOPN -
TOPN returns the top N rows of the specified table.
Box 4: [TransactionDate]
TOPN Syntax: TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]ג€¦])
The orderBy_expression: Any DAX expression where the result value is used to sort the table and it is evaluated for each row of table.
Reference:
https://docs.microsoft.com/en-us/dax/topn-function-dax
by sabribrk at March 11, 2021, 6:40 p.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
sabribrk
Highly Voted 4 years, 3 months ago
Calculate SUM TOPN [TransactionDate]
upvoted 154 times
rouphail
4 years ago
but SUM takes an argument just a column, as per the documentation ... and this is not the case here where the argument, is a table followed by EXPR, so it should be SUMX
upvoted 5 times
Rob77
4 years ago
It is only ONE argument passed to the function (Transactions[Amount]) Note closed with ")" bracket.
upvoted 4 times
...
Dummy92yash
2 years, 9 months ago
Top n function is part of calculate function
upvoted 1 times
...
...
Canary_2021
3 years, 11 months ago
Like this answer: Calculate SUM TOPN TransactionDate
upvoted 12 times
hendriktytgatpwc
3 years, 11 months ago
instead of transaction Date use transaction ID because you with the date you will return the total sales valye of the latest 50 days not transactions (use transaction id instead)
upvoted 5 times
ExamTopicsU10
3 years, 9 months ago
I believe it has to be the transaction date: in the measure, you are asking for the first 50 lines from the transaction table, ordered by the date value.
upvoted 7 times
...
...
...
biosedu
4 years, 1 month ago
If Transaction Date has datatype DATE/TIME this answer is correct, but if Transaction Date has datatype DATE, you have several transactions in a same date, so you need choose Transaction ID in the last option.
upvoted 5 times
...
Zakriya
4 years, 1 month ago
True. Here 2nd option should be SUM and not SUMx.
upvoted 2 times
Zakriya
4 years, 1 month ago
And last option should be TransactionID. Because TransactionDate may give more than 50 latest transactions if a single day has more than one transactions.
upvoted 9 times
...
...
Load full discussion...
...
VidSicious
Highly Voted 4 years, 1 month ago
So it Is: CALCULATE SUM TOPN TransactionID
upvoted 24 times
Zered
2 years, 11 months ago
No it should be, CALCULATE SUM TOPN TransactionDate. Top 50 by TransactionID doesn't always gives you the latest transactions. In this case, you can only sort by TransactionDate (desc) and pick the Top 50 from the list.
upvoted 3 times
...
ikok
3 years, 6 months ago
Tested it with dummy data, and this is correct answer. Question asked latest 50 transaftions, not latest 50 days transations (very likely more than 50 transactions when multiple transactions on one day).
upvoted 5 times
Matreshka
3 years ago
the latest 50 transactions, not 50 days. So I agree
upvoted 1 times
...
Nurgul
3 years, 4 months ago
There is no comma after TransactionID. So maybe the correct choice would be TransactionDate?
upvoted 1 times
...
mss1
2 years, 10 months ago
I agree, when you have 60 transactions in one day, you want it sorted on transactionID and not date
upvoted 1 times
...
...
...
Tomaszthomas
Most Recent 2 years, 7 months ago
CALCULATE SUM (there is only one argument in this function between (). SUMX would require two. TOPN [TransactionDate]
upvoted 1 times
...
Teymietee
2 years, 9 months ago
Sum and not Sum should be used
upvoted 1 times
Teymietee
2 years, 9 months ago
Sum and not SumX rather
upvoted 1 times
...
...
rainbowyu
2 years, 9 months ago
Calculate (Apply the filter, in SumX) Sumx (Sum can have only one variable) TopN TransactionID ( This is because there could be more than 50 on the same date)
upvoted 2 times
...
Gecig
2 years, 10 months ago
It cannot be SUMX. Syntax : - SUMX(<table>, <expression>) In the question, it is directly passing Column name and then there is no 2nd argument. After column name, there is ')' closed. Hence, based on syntax, it should be SUM() only.
upvoted 3 times
...
Mazarakis
2 years, 11 months ago
The right answer is : Calculate SUM TOPN [TransactionDate] SUMX cannot be because want 2 arguments
upvoted 1 times
...
NhiN
3 years, 2 months ago
The question mentioned nothing about TransactionID 's data type (string or integer) that we can use it in the order by expression in TOPN. Moreover, TopN function returns the top N rows of the specified table, based on Order by expression. Many people here mentioned the case that there would be more than 50 transaction on the latest date. If that's the case, it would only return 50 rows. No way it returns 51 rows or more.
upvoted 1 times
...
TechDiva
3 years, 2 months ago
I got it on my exam 03/17/2022. 90-95% questions were from here. 61 questions in 100 min. All 3 case studies that appeared in exam, where from here.
upvoted 4 times
Xavich
3 years, 1 month ago
can you please stfu - you dont need to write the same sht on every question
upvoted 13 times
...
Mabuse1
1 year, 10 months ago
a very useful note. Thanks for noting.
upvoted 1 times
...
...
Letsgo9999
3 years, 3 months ago
In exam 07-03-2022
upvoted 1 times
...
123CCM
3 years, 3 months ago
I think the answer shoud be something like this: Revenue Last 50 Transactions = TOPN(50, SUMMARIZE(Transactions,Transactions[TransactionID],"Transactions",SUM(Transactions[Amount])), Transactions, DESC) Nothing else makes sense for me.
upvoted 2 times
...
kwanalytics
3 years, 5 months ago
On exam Dec. 27, 2021 - answered Calculate SUM TOPN [TransactionDate]
upvoted 1 times
...
MustPassDA100
3 years, 5 months ago
on exam 12/25/2021
upvoted 1 times
...
smario
3 years, 5 months ago
Correct Answer: 1.Calculate 2.SUM( because we have only 1 paramter passed to it, hint closed bracket after amount, if it was sumx there would'nt be a closed bracket. 3.TOPN 4.TransactionDate ( not transid because it does not have any comma before desc also they just want to see the latest 50 transaction, even thought the last 50 transaction could be on the same day)
upvoted 4 times
96sudu
3 years, 4 months ago
For point 4. the answer contradicts the statement. If the last 50 transactions are the same date, the TopN filter when used with TransactionDate will return the latest 50 dates' transactions. So it will be the 50 transactions on the lastest date plus transactions from the other 49 latest dates!
upvoted 2 times
...
...
PatrickStr
3 years, 6 months ago
on exam 12/10/2021
upvoted 1 times
...
aguilartu1
3 years, 7 months ago
on exam - Nov 11, 2021. my answers: CALCULATE SUM TOPN TRANSACTIONDATE
upvoted 2 times
...
hanchihl
3 years, 7 months ago
It mus be SUM, because SUMX need an expression
upvoted 2 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