Sometimes it might be handy to search dimensions members in MDX. The MDX below will show you an example on how to get all products that contains the name ‘Bike’
SELECT [Measures].[Sales Amount] on 0, ( Filter( [Product].[Product].[Product].ALLMEMBERS, Instr( [Product].[Product].currentmember.Properties( 'Member_Caption' ), 'Bike' ) > 0 ) ) on 1 from [Adventure Works]
And here is some other examples
List products that does not contain the word ‘Bike’
Instr( [Product].[Product].currentmember.Properties( 'Member_Caption' ), 'Bike' ) = 0
List products that is named ‘Bike Wash – Dissolver’
Instr( [Product].[Product].currentmember.Properties( 'Member_Caption' ), 'Bike Wash - Dissolver' ) = 1
Cheers,
Sindre
A blog about Analytics, AWS and Azure 
MDX – search by member name:
How can I filter/search by the name of the member and not its ID. I need a simple way to replace ID “1002” (that can vary in a different environment) to the string “Apple” – meaning, instead of writing “1002” I will write “Apple”.
Suggestions?
Thank you
with
member [Measures].[Apple_Active] as
([Device App Version].[App Name].&[1001],–TypeA
[Model and Manufacturer].[Manufacturer].&[1002],–Apple
[Measures].[Number of Active Users Past 30 Days])
select
[Measures].[Apple_Active]
on 0
from users
Hi!
To search for key instead of caption you can write something like this
SELECT [Measures].[Sales Amount] on 0,
(
Filter(
[Product].[Product].[Product].ALLMEMBERS,
Instr( [Product].[Product].currentmember.Properties( ‘Member_Key’ ), ‘559’ ) > 0
)
) on 1
from [Adventure Works]
Link to documentation: https://msdn.microsoft.com/en-us/library/ms144821.aspx
Kind regards,
Sindre