Please go to this link if you can (it was down earlier):http://blogs.adatis.co.uk/blogs/shaunryan/archive/2011/05/05/multi-valued-attributes-in-sql-server-analysis-services-ssas.aspx

This provides a good example for my situation. In this example, you will see a Movie dimension with four attributes; Genre, Language, Movie, and Theme. I have a similar setup except mine is Top Level Hierarchy>Categories>Values which are all under the one hierarchy.

My Question

I have the dimension setup as a multi-value parameter in one of my reports. When I filter on a value in Genre and in Language, it provides all values from that genre and all values from that language. I really only want the values that include both.

Genre - Western: Movie1, Movie2, Movie3

Language - English: Movie2, Movie4, Movie5

If I filter on Western and English, I get Movie1-5 when all I really want is Movie2 only. Is there any way to have this do an Intersect within the same dimension or do I have to build each one out into its own dimension?

Thank you. 

How about using a sub-cube like this? Using AdventuresWorks2012 cube.

select 
{[Measures].[Internet Sales Amount]}
on 0,
{
	filter
	([Product].[Product Categories].[Product].members, [Measures].[Internet Sales Amount])
} 
on 1
from 
(
	select 
	filter
	([Product].[Product Categories].[Product].members
	, [Product].[Product Categories].Properties( "Color" ) = "Black")
	on 0
	from 
	(
		select 
		filter
		([Product].[Product Categories].[Product].members
			, [Product].[Product Categories].Properties( "Size" ) = "42")
		on 0
		from [Adventure Works]
	)
)

This did not work for me. I get expected results back when I just include the first filter after FROM. As soon as I add the second filter I get no results even though I used two values that would be associated to the same piece of data. I also had to use Name as the property in order for it to work.

Do the two properties/fields that you are trying to filter on, have an attribute relationship in the dimension? Like the below?

Also does the second filter work on its own?

What do you mean by 'had to use Name as the property'? Can you add a snippet of your MDX query?

Hi MP4SPO,

According to your description, you want to filter the movies where the Genre is Western AND Language is English. Right?

In this scenario, you can just apply the AND logic in the FILTER() function to get the corresponding members. It's equivalent as using sub select as Shri suggests. Please check the Properties Name and the values data type. For example: the Size for product in AdventureWorks sample database is string instead of integer.

Please see the sample query and result below:

select [Measures].[Internet Sales Amount] on 0,
filter([Product].[Product Categories].[Product].members,
[Product].[Product Categories].properties("Color")="Black" 
and 
[Product].[Product Categories].properties("Size")="42"
) on 1
from
[Adventure Works]

Regards,