Exam PL-300 All Questions

View all questions & answers for the PL-300 exam

Exam PL-300 topic 7 question 2 discussion

Actual exam question from Microsoft's PL-300

Question #: 2
Topic #: 7

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

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

Submit Cancel

ThariCD

Highly Voted 2 years, 9 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 98 times

charles879987

2 years, 4 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 6 times

28485e1

1 year, 3 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, 4 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, 10 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, 9 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

...

Jasmine121

Most Recent 4 months, 1 week ago

ALLEXCEPT (Table, Col) so it is wrong

upvoted 1 times

...

SylUK

4 months, 2 weeks 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

9 months, 1 week ago

Answer should be: CALCULATE ALL DIVIDE I tested it. ALLEXCEPT doesn't work with these arguments.

upvoted 2 times

...

HenryBiz

1 year 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, 1 month 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, 2 months ago

the variable assigned is for the denominator , so ALL makes more sense to me

upvoted 2 times

...

Dani_eL

1 year, 3 months ago

ALLEXCEPT (Customer, Customer[Continent] ) requires 2 parameters!!!!

upvoted 6 times

...

Dani_eL

1 year, 3 months ago

CALCULATE() ALL() DIVIDE()

upvoted 3 times

...

cs3122

1 year, 5 months ago

CALCULATE() ALL() DIVIDE()

upvoted 4 times

...

c02faba

1 year, 6 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

...