nerdexam
Microsoft

DP-500 · Question #30

You have a Power BI dataset that contains the following measure. YTD Year-over-Year Var = DIVIDE ( ( [Sales Amount] - CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ), 'Calendar'[Flag] =…

The correct answer is A. Replace both calculate functions by using a variable that contains the calculate function. To improve performance without changing logic, the repeated CALCULATE expression should be stored in a DAX variable and reused.

Implement and manage data models

Question

You have a Power BI dataset that contains the following measure. YTD Year-over-Year Var = DIVIDE ( ( [Sales Amount] - CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ), 'Calendar'[Flag] = "YTD" ) ), CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ), 'Calendar'[Flag] = "YTD" ), BLANK() ) You need to improve the performance of the measure without affecting the logic or the results. What should you do?

Exhibit

DP-500 question #30 exhibit

Options

  • AReplace both calculate functions by using a variable that contains the calculate function.
  • BRemove the alternative result of blank( ) from the divide function.
  • CCreate a variable and replace the values for [Sales Amount].
  • DRemove "calendar[Flag] = "YTD" from the code.

How the community answered

(45 responses)
  • A
    80% (36)
  • B
    13% (6)
  • C
    2% (1)
  • D
    4% (2)

Why each option

To improve performance without changing logic, the repeated `CALCULATE` expression should be stored in a DAX variable and reused.

AReplace both calculate functions by using a variable that contains the calculate function.Correct

The `CALCULATE` expression `CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ), 'Calendar'[Flag] = "YTD" )` is repeated twice within the `DIVIDE` function. By assigning this complex expression to a variable (VAR), DAX can evaluate it once and reuse the result, thereby improving the measure's performance by avoiding redundant calculations without altering its logic or results.

BRemove the alternative result of blank( ) from the divide function.

Removing `BLANK()` from the `DIVIDE` function would change the measure's behavior when the denominator is zero or blank, affecting its logic.

CCreate a variable and replace the values for [Sales Amount].

`[Sales Amount]` is a simple reference, and replacing it with a variable would offer negligible performance improvement compared to optimizing the repeated `CALCULATE` expression.

DRemove "calendar[Flag] = "YTD" from the code.

Removing `'Calendar'[Flag] = "YTD"` would alter the filter context of the `CALCULATE` function, fundamentally changing the measure's logic and results.

Concept tested: DAX variable for performance optimization

Source: https://learn.microsoft.com/en-us/dax/var-dax

Topics

#DAX#Performance Optimization#Variables (VAR)#Measures

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DP-500 Practice