Filtering structure across 2 dimensions

I would appreciate any help with the following problem:

Other than cube redesign, which I know we can accomplish, if theres an actual way through MDX to filter structure between 2 dimensions. As of right now I can only accomplish filtering measures by 2 dimensions. Example:

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

What occurs is all members from Dimension1 is returned and the Measures are filtered accordingly. What I want is it to only bring back the rows which have a relationship with Dimension2. Is this possible?

But if I try to filter structure across 2 different heirarchies in the same dimension it works fine.

SELECT { [Measures].Members } ON COLUMNS,
{ [Dimension1].[Heirarchy2].[AnyHeirarchyLevel].Members } ON ROWS
FROM [Planogram]
WHERE ( [Dimension1].[Heirarchy1].[AnyHeirarchyLevel].[AnyMember] )

This only returns rows in which the two heirarchy members are related.

So obvisouly If I moved my 2nd Dimension under the 1st Dimesion and make it a heirachy it would work, but any other way around this?

Thanks a lot.

When you say: "only bring back the rows which have a relationship with Dimension2", are they only related via the fact table, or in some other way? In the former case, try Non Empty, like:

SELECT { [Measures].Members } ON COLUMNS,
Non Empty [Dimension1].[AnyHeirarchy].[AnyHeirarchyLevel].Members ON ROWS
FROM [Planogram]
WHERE ( [Dimension2].[AnyHeirarchy].[AnyHeirarchyLevel].[AnyMember] )

|||

Thanks for the reply.

Yes, they are only related via the fact table. If I try non empty, and please correct me if im wrong, that will filter by measures (sales, units, etc), and only show members that have sales/units. In essence exluding the real relationships (members that have no sales won't be shown)

I would like to bring back every member that is related to Dimension 2's member, regardless of no sales or not.

Thanks.

|||

The fact of sales is a relation between dimension1 and dimension2. There isn't another relation between dimension1 and dimension2.

If you have another one the it should exists as another fact table or anything else.