SSAS MDX: Filter

We can filter an axis based on the members, or based on the measure value.

Based on the members: begins with F or after      Based on the measure value: Sales > $40,000

To filter based on members, we can use string comparison:

select non empty
filter
( {[Customer].[City].[All].Children},
[Customer].[City].CurrentMember.Name > “F”
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

Or use string functions:

select non empty
filter
( {[Customer].[City].[All].Children},
left([Customer].[City].CurrentMember.Name,1) = “F”
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

To filter based on measure value, we use numeric comparison:

select non empty
filter
( {[Customer].[City].[All].Children},
[Measures].[Internet Sales Amount] > 40000
) on 1,
[Measures].[Internet Sales Amount] on 0
from [Adventure Works];

Other examples of string functions:

Second letter is A:
mid([Customer].[City].CurrentMember.Name,2,1) = “A”

Ends with A:
right([Customer].[City].CurrentMember.Name,1) = “A”

Begins with O and ends with A:
left([Customer].[City].CurrentMember.Name,1) = “O” and
right([Customer].[City].CurrentMember.Name,1) = “A”