Calculated SSAS Member based on multiple dimension attributes

Question

I'm attempting to create a new Calculated Measure that is based on 2 different attributes. I can query the data directly to see that the values are there, but when I create the Calculated Member, it always returns null.

Here is what I have so far:

CREATE MEMBER CURRENTCUBE.[Measures].[Absorption]
 AS sum
(

    Filter([Expense].MEMBERS, [Expense].[Amount Category] = "OS"
           AND ([Expense].[Account Number] >= 51000 
           AND [Expense].[Account Number] < 52000))
    ,

    [Measures].[Amount - Expense]
), 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Expense';     

Ultimately, I need to repeat this same pattern many times. A particular accounting "type" (Absorption, Selling & Marketing, Adminstrative, R&D, etc.) is based on a combination of the Category and a range of Account Numbers.

I've tried several combinations of Sum, Aggregate, Filter, IIF, etc. with no luck, the value is always null.

However, if I don't use Filter and just create a Tuple with 2 values, it does give me the data I'd expect, like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Absorption]
 AS sum
(

    {( [Expense].[Amount Category].&[OS], [Expense].[Account Number].&[51400]  )}
    ,

    [Measures].[Amount - Expense]
), 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Expense';  

But, I need to specify multiple account numbers, not just one.

Answer 1

In general, you should only use the FILTER function when you need to filter your fact table based on the value of some measure (for instance, all Sales Orders where Sales Amount > 10.000). It is not intended to filter members based on dimension properties (although it could probably work, but the performance would likely suffer).

If you want to filter by members of one or more dimension attributes, use tuples and sets to express the filtering:

CREATE MEMBER CURRENTCUBE.[Measures].[Absorption]
AS 
    Sum( 
       {[Expense].[Account Number].&[51000]:[Expense].[Account Number].&[52000].lag(1)} *
       [Expense].[Amount Category].&[OS],
       [Measures].[Amount - Expense]
    ), 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Expense';

Here, I've used the range operator : to construct a set consisting of all [Account Number] members greater than or equal to 51000 and less than 52000. I then cross-join * this set with the relevant [Amount Category] attribute, to get the relevant set of members that I want to sum my measure over.

Note that this only works if you actually have a member with the account number 51000 and 52000 in your Expense dimension (see comments).

An entirely different approach, would be to perform this logic in your ETL process. For example you could have a table of account-number ranges that map to a particular accounting type (Absorption, Selling & Marketing, etc.). You could then add a new attribute to your Expense-dimension, holding the accounting type for each account, and populate it using dynamic SQL and the aforementioned mapping table.