nerdexam
Microsoft

70-466 · Question #53

You are developing a SQL Server Analysis Services (SSAS) cube. You need to add a calculated member to the Customer dimension to evaluate the sum of values for France and Germany. Which expression shou

The correct answer is B. SDM({[Customer].[Customer Geography].[Country].&[France]},{[Customer].[Customer Geography].[Country].&[Germany]}). A calculated member summing two specific countries must pass each country as a separate set argument to the aggregation function, not as a single combined set.

Build an analysis services database

Question

You are developing a SQL Server Analysis Services (SSAS) cube. You need to add a calculated member to the Customer dimension to evaluate the sum of values for France and Germany. Which expression should you use? (To answer, drag the appropriate expression to the answer area.)

Options

  • A{[Customer].[Customer Geography].[Country].&[France], [Customer].[Customer Geography].[Country].&[Germany]}
  • BSDM({[Customer].[Customer Geography].[Country].&[France]},{[Customer].[Customer Geography].[Country].&[Germany]})
  • CSDM({[Customer].[Customer Geography].[Country].&[France], [Customer].[Customer Geography].[Country].&[Germany]})
  • DSDM({[Customer].[Customer Geography].[State].&[QLD], [Customer].[Customer Geography].[State].&[NSW]}, {[Customer].[Customer Geography].[State].&[VIC]})

How the community answered

(46 responses)
  • A
    24% (11)
  • B
    59% (27)
  • C
    11% (5)
  • D
    7% (3)

Why each option

A calculated member summing two specific countries must pass each country as a separate set argument to the aggregation function, not as a single combined set.

A{[Customer].[Customer Geography].[Country].&[France], [Customer].[Customer Geography].[Country].&[Germany]}

This expression constructs a set literal containing both France and Germany but does not wrap it in any aggregation function, so no summation is performed.

BSDM({[Customer].[Customer Geography].[Country].&[France]},{[Customer].[Customer Geography].[Country].&[Germany]})Correct

Option B correctly applies the aggregation function to France and Germany by passing them as two distinct set expressions, which evaluates and sums each country member independently using the member key syntax (&[France] and &[Germany]) to ensure exact matching on the Customer Geography hierarchy. This two-argument form produces the correct summed result for the pair of countries.

CSDM({[Customer].[Customer Geography].[Country].&[France], [Customer].[Customer Geography].[Country].&[Germany]})

This passes both France and Germany combined into a single set as one argument, which is the incorrect syntax form for this function - the two countries must be supplied as separate set arguments.

DSDM({[Customer].[Customer Geography].[State].&[QLD], [Customer].[Customer Geography].[State].&[NSW]}, {[Customer].[Customer Geography].[State].&[VIC]})

This expression references Australian state-level members (QLD, NSW, VIC) at the wrong hierarchy level instead of the France and Germany country members required by the question.

Concept tested: SSAS MDX calculated member aggregation over specific dimension members

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-calculated-members-building-calculated-members

Topics

#MDX calculated members#set aggregation#Customer dimension#country grouping

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice