DAX equivalent for MDX Calculated Member

Question

We have a SSAS 2012 tabular model at our disposal which we are reporting on with Tableau. Currently, we have a dimension [Material] with an attribute [Department]. What we're trying to do is create an MDX calculated member equivalent in DAX for implementing a custom grouping of the [Department] attribute.

For instance:

CREATE MEMBER [Material].[Department].[AB] AS
    AGGREGATE({[Material].[Department].&[A], [Material].[Department].&[B]})

We tried to add a calculated member [AB] in hierarchy [Material].[Department] in Tableau with this MDX expression:

AGGREGATE({[Material].[Department].&[A], [Material].[Department].&[B]})

At design time, the expression validates but when we try using that member in our report we get an error stating that this kind of MDX calculated members are not supported in DAX.

Does anyone know of an equivalent or if and how we should get this MDX member to work?

Answer 1

Use SUMX. I would imagine your DAX measure would look similar to:

=SUMX(FILTER('Material', 'Material'[Department]=OR("A","B")),[MaterialQuantity])