Complex Filter on SSAS calculated Member

Question

I Tried to create a calculated measure inn my SSAS cube with complex filters as the following:

([Measures].[Amount],[Scenarios].[Scenario Key].&[1],[AccountType],[AccountType].[Account Type].&[Bank],[AccountType].[Account Type].&[Cash],[AccountType].[Account Type].&[NotesReceivable],[JE Type].[JE Type].&[CI],[JE Type].[JE Type].&[NR])

I want to Get the summation of amount value which has:

1. Scenario Key = 1
2. Account Type IN ("Bank","Cash","NotesReceivable")
3. JE Type IN ("CI","NR")

But i Get this measure as Null So can any one can help to solve this?

Answer 1

It's not working because you are doing an intersection of the same dimension hierarchy: [AccountType].[Account Type].&[Bank],[AccountType].[Account Type].&[Cash]

means in MDX: Account Type = Bank AND Account Type = Cash at the same time.

Just use set of members and SUM function.

Try this one:

SUM(
{[AccountType].[Account Type].&[Bank]
,[AccountType].[Account Type].&[Cash]
,[AccountType].[Account Type].&[NotesReceivable]}
*
{[JE Type].[JE Type].&[CI]
,[JE Type].[JE Type].&[NR]}

,([Scenarios].[Scenario Key].&[1],[Measures].[Amount]))

Explanation:

• SUM - aggregate function
• Dimension1 filter * Dimension2 filter - gave all combinations
• (Dimension3,measure) - filters out single-selected dimensions