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, 11 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 99 times
charles879987
2 years, 5 months 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 7 times
28485e1
1 year, 5 months 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, 5 months 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, 11 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 4 times
...
...
...
...
fdsdfgxcvbdsfhshfg
Highly Voted 2 years, 11 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
...
Lulu_2022
Most Recent 1 month, 1 week ago
VAR AllCategoryRev = CALCULATE(SUM([Revenue]), ALL(ProductList[ProductCategory])) RETURN DIVIDE(SUM([Revenue]), AllCategoryRev)
upvoted 1 times
...
Jasmine121
5 months, 3 weeks ago
ALLEXCEPT (Table, Col) so it is wrong
upvoted 1 times
...
SylUK
6 months 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
10 months, 3 weeks ago
Answer should be: CALCULATE ALL DIVIDE I tested it. ALLEXCEPT doesn't work with these arguments.
upvoted 2 times
...
HenryBiz
1 year, 2 months 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
1 year, 3 months 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
1 year, 4 months ago
the variable assigned is for the denominator , so ALL makes more sense to me
upvoted 2 times
...
Dani_eL
1 year, 5 months ago
ALLEXCEPT (Customer, Customer[Continent] ) requires 2 parameters!!!!
upvoted 6 times
...
Dani_eL
1 year, 5 months ago
CALCULATE() ALL() DIVIDE()
upvoted 3 times
...
cs3122
1 year, 6 months ago
CALCULATE() ALL() DIVIDE()
upvoted 4 times
...
c02faba
1 year, 7 months 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, 8 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
2 years, 3 months ago
CALCULATE, ALL, DIVIDE. Tested - working.
upvoted 13 times
...
Outsider
2 years, 3 months ago
REMOVEFILTERS = ALL. So, Calculate. All and DIVIDE>
upvoted 3 times
...
SanaCanada
2 years, 4 months ago
Correct Answer Calculate, All, Divide No confusion, and no need to discuss further
upvoted 6 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