Add multiple calculating filters in MDX

Question

I am really new to MDX but I have spent the past two days looking for an answer but failed. So I greatly appreciate your help and patience.

I am trying to query a Cube with filters on multiple dimensions, and I realize that there are many similar questions already there, like this or this.

The thing is, instead of specifying a particular content I am looking for, I am trying to set up filters that picks up all records that begins with a specific string. This requires left function in the filters (i.e. calculating filters?) but I cannot blend them nicely into the code.

My failed code is like this (the two filters should be in ANDrelation)

Select Non Empty ([Measures].[Sales]) ON 0
FROM [Cube_Name]
WHERE
(
FILTER
(
[Customer].[CustomerID].Members, Left([Customer].[CustomerID].CurrentMember.Name,4)="ABCD"),
[Product].[ProductID].Members, Left([Product].[ProductID].CurrentMember.Name,3)="EFG")
)
)

(My trial is based on the last answer here.)

I also read that there are some workarounds like CROSSJOIN WITH AGGREGATE or Sub-SELECT, but I just do not have any clue on 1)how to incorporate the conditions inside; 2) performance (I heard that CROSSJOIN can be slow).

I am not sure if I should mention it here, but I am actually implementing the MDX from Excel VBA by using the ADOMB.Cellset object. It only gives me the Grand total of the query I implemented under Cellset.Items(0) (there are no more items).

Thank you!

Answer 1

You need to split two sets into two filters:

Select 
Non Empty [Measures].[Sales] on 0
From [Cube_Name]
Where
(
    Filter(
        [Customer].[CustomerID].[CustomerID].Members, 
        Left(
            [Customer].[CustomerID].CurrentMember.Name,
            4
        ) = "ABCD"
    ),
    Filter(
        [Product].[ProductID].[ProductID].Members, 
        Left(
            [Product].[ProductID].CurrentMember.Name,
            3
        ) = "EFG"
    )
)