DAX equivalent for MDX Calculated Member

5
votes

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?

2
mdxbusiness-intelligencetableau-apidax
Would you mind posting an example of [Department] members and for each, the expected result in the calculated member? – MarkD
Did you have a solution ? I have the same issue and may be your solution could help me. Thank you – Lidou123

2 Answers

1
votes

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

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

A Tabular model does not support the creation of calculated members in the model. The best you can do is to use a query-scoped calculated member by defining it in the MDX query. E.g. 

WITH MEMBER [Material].[Department].[All].[AB] AS
  AGGREGATE({[Material].[Department].&[A], [Material].Department].&[B]})
SELECT ...
FROM ...