MDX Filter dimension members and attributes

Sometimes we need to filter members of a dimension based on value of one attribute and fetch value of another attribute of the same member.

This is relatively simple in a query, like so:

WITH

   MEMBER [Measures].[Label] AS [Geography].[Country].CURRENTMEMBER.MEMBER_CAPTION

SELECT {[Measures].[Label]} ON COLUMNS,

       NON EMPTY { ([Geography].[State-Province].&[FL]&[US] ) } ON ROWS

FROM  [Adventure Works]

However notice that we need to know the country code 'US' to specify state Florida's value. The query gets even more complex when we only know state code 'FL' and want to use the country label as a calculated measure. Here we need to filter the dimension members based on one attribute value and return caption of another attribute of the same dimension member.

So the query changes to:

WITH

   MEMBER [Measures].[Label] AS (filter(

   [Geography].[Country].children * [Geography].[State-Province].children, Instr( [Geography].[State-Province].currentmember.Properties( 'Member_Caption' ), 'FL')  > 0)

   ).item(0).item(0).membervalue

SELECT {[Measures].[Label]} ON COLUMNS

FROM  [Adventure Works]

And the result is:

More reference on querying dimension members:

https://www.purplefrogsystems.com/blog/2012/11/querying-dimension-members-in-mdx/