Search for dimensions members in MDX using inStr()

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

Advertisements

2 thoughts on “Search for dimensions members in MDX using inStr()

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s