The actual question takes the form of:
"I have Dimension A and Dimension B, related to Measure 1. Dimension B is also related to Measure 2. How can I (is it possible to) get the values for Measure 1 filtered/sliced by Dimension A. I know it is easy to achieve with a join in SQL, but I do not know how to do it with MDX.
This suggest the following dimension model:
One solution would be creating a many-to-many relationship between Dimension A and Measure Group 2. However, we may want to avoid that for some reason and answer the problem with a query.
We can achieve the desired result in a number of ways but I will discuss the one using NONEMPTY. Others would be using FILTER and EXISTS.
A sample script is:
SELECT
{
[Measures].[Measure 2]
} ON 0,
NON EMPTY
{
NONEMPTY( [Dimension B].[Dimension B Hierarchy].Members,
([Measures].[Measure 1], [Dimension A].[Dimension A Hierarchy].&[Member_Key]))
} ON 1
FROM [Cube]
What this script does:
1. Gets all Dimension B members, which have associated cells for Measure 1 and the specific Dimension A member (which we are filtering/slicing by)
2. Gets the Measure 2 cells for the set of members retrieved in Step 1
3. Removes members from Step 1, for which cells from Step 2 are empty
An AdventureWorks example is:
SELECT
{
[Measures].[Internet Order Count]
} ON 0,
NON EMPTY
{
NONEMPTY( [Product].[Product].Members,
([Measures].[Reseller Order Count], [Reseller].[Reseller].&[238]))
} ON 1
FROM [Adventure Works]
