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

Exam PL-300 All Questions

View all questions & answers for the PL-300 exam
Go to Exam

Exam PL-300 topic 7 question 2 discussion

Actual exam question from Microsoft's PL-300
Question #: 2
Topic #: 7
[All PL-300 Questions]

HOTSPOT -
You need to create a measure that returns the percent of revenue by product category.
How should you complete the measure? 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 -
CALCULATE evaluates an expression in a modified filter context.
Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> [, ג€¦]]])

Box 2: REMOVEFILTERS -
REMOVEFILTERS clear filters from the specified tables or columns.

Box 3: DIVIDE -
DIVIDE performs a division.
Example: MEASURE FactInternetSales[%Sales] = DIVIDE([TotalSales], CALCULATE([TotalSales],REMOVEFILTERS()))
Note: The RETURN keyword consumes variables defined in previous VAR statements.
Reference:
https://docs.microsoft.com/en-us/dax/calculate-function-dax
https://docs.microsoft.com/en-us/dax/removefilters-function-dax https://dax.guide/st/return/
by ThariCD at Sept. 13, 2022, 8:27 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
ThariCD
Highly Voted 2 years, 5 months ago
Explanation and answer incorrect for the second function, REMOVEFILTERS in the explanation isn't part of the options, instead it should be ALL to remove any context from the calculation. So the final formula should be VAR AllCategoryRev = CALCULATE(SUM([Revenue]), ALL(ProductList[ProductCategory])) RETURN DIVIDE(SUM([Revenue]), AllCategoryRev)
upvoted 96 times
charles879987
2 years ago
ALL should not be used because if All Category Revenue will always be equal to sum of revenue since all categories are added up. AllExcept should be used because it will preserve the filter on a selected product category( i.e. Slicer or row context) and thus return the revenue on a particular category only.
upvoted 6 times
28485e1
11 months, 2 weeks ago
This was very helpful. We need to think that we're calculating one category as percent of total revenue, so applying ALLEXCEPT is the way we achieve that.
upvoted 3 times
...
charles879987
2 years ago
ALL should not be used because All Category Revenue will always equal to the totalsum of revenue since all categories are added up. So the result of the Divide will be 1. AllExcept should be used because it will preserve the filter on a selected product category( i.e. Slicer or row context) and thus return the revenue on a particular category only.
upvoted 3 times
taod
1 year, 5 months ago
I don't agree. We neet a total sum in the denominator to receive the ratio. In the documentation of ALLEXCEPT, this specific case is even explained as a usecase for ALL: ALL: This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value. https://learn.microsoft.com/en-us/dax/allexcept-function-dax
upvoted 3 times
...
...
...
...
fdsdfgxcvbdsfhshfg
Highly Voted 2 years, 5 months ago
ALL[ProductCategory] is the correct option in the second dropdown. ALLEXCEPT - Removes all context filters in the table except filters that have been applied to the specified columns; so It's wrong and we wouldn't get All the revenue for the divide measure, but only row level ProductCategory revenue
upvoted 18 times
...
SylUK
Most Recent 1 week, 1 day ago
The given answer is correct : CALCULATE , ALLEXCEPT , and DIVIDE. see this:https://learn.microsoft.com/en-us/dax/all-function-dax
upvoted 1 times
...
539d541
5 months ago
Answer should be: CALCULATE ALL DIVIDE I tested it. ALLEXCEPT doesn't work with these arguments.
upvoted 2 times
...
HenryBiz
8 months, 2 weeks ago
CALCULATE ALL DIVIDE "All" is correct because of the grand total needed, because of the number of the arguments required by this function.
upvoted 2 times
...
SIH007
9 months, 1 week ago
ALLEXEPT is correct, I tried it. When using ALL you get the error message "Multiple table arguments are not allowed in the ALL/ALLNOBLANKROW/REMOVEFILTERS function."
upvoted 1 times
...
ab97776
10 months, 2 weeks ago
the variable assigned is for the denominator , so ALL makes more sense to me
upvoted 2 times
...
Dani_eL
11 months, 2 weeks ago
ALLEXCEPT (Customer, Customer[Continent] ) requires 2 parameters!!!!
upvoted 6 times
...
Dani_eL
11 months, 2 weeks ago
CALCULATE() ALL() DIVIDE()
upvoted 3 times
...
cs3122
1 year ago
CALCULATE() ALL() DIVIDE()
upvoted 4 times
...
c02faba
1 year, 1 month ago
ALLEXCEPT doesn't work. The argument of ALLEXCEPT should be: ALLEXCEPT(Table, Column1 [,Column2]...). I tested it and it gives me the following error: 'Too few arguments were passed to the ALLEXCEPT function. The minimum argument count for the function is 2.' ALL works without error: ALL (Column[, Column[, …]]) https://learn.microsoft.com/en-us/dax/all-function-dax
upvoted 5 times
...
_Dirk_
1 year, 2 months ago
Calculate, All, Divide It can't be allexpect because all expect has a table argument AND a column argument seperated by a comma.
upvoted 3 times
...
UlyUkr
1 year, 9 months ago
CALCULATE, ALL, DIVIDE. Tested - working.
upvoted 12 times
...
Outsider
1 year, 9 months ago
REMOVEFILTERS = ALL. So, Calculate. All and DIVIDE>
upvoted 3 times
...
SanaCanada
1 year, 11 months ago
Correct Answer Calculate, All, Divide No confusion, and no need to discuss further
upvoted 6 times
...
youssef_yt89
2 years, 1 month ago
CALCULATE ALL DIVIDE
upvoted 8 times
...
jsking
2 years, 1 month ago
Anyone with experience in DAX would only need to read the question in these case study questions. The questions seem to hint the answer already.. for this question for example anyone would know that the moment you see a percentage of the total is required you would immediately go with the ALL function and the rest is easy. CALCULATE, ALL, DIVIDE
upvoted 10 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