MDX Intersection - how to?

Question

A similar question has been asked here, but I'll give a brief intro. In SQL Server there are 3 tables:

1. Order (Id, Date);
2. Product (Id, Name);
3. OrderRefProduct (Id, OrderId, ProductId);

OrderRefProduct is a many-to-many table since one product can be in different orders and different products can be in one order.

All this is sticked into an OLAP Cube with Orders and Products as Dimensions and OrderRefProduct as Facts. We need to get results like the output of SQL query would provide:

SELECT ProductId, COUNT(*) as [Count]
FROM OrderRefProduct 
GROUP BY ProductId

the correspondind MDX query is fairly simple, given the Cube is built correctly:

SELECT 
NON EMPTY { [Measures].[Order Ref Product Count] } ON COLUMNS, 
NON EMPTY { ([Product].[Product ID].[Product ID].ALLMEMBERS ) } ON ROWS 
FROM [Sales]

And the result for both is:

ProductId   Count
1   7
2   7
3   5
4   6

Now here's the problem. I want to select count of orders that have BOTH products 3 AND 4. SQL:

select ProductId, Count(*) as [Count]
from OrderRefProduct
where [OrderId] in 
(SELECT S.OrderID FROM 
(select * from OrderRefProduct where ProductID=3) as S
INNER JOIN (select * from OrderRefProduct where ProductID=4) as T on T.OrderId = S.OrderID)
Group by ProductId

Result:

ProductId   Count
1   1
2   1
3   3
4   3

Can't reproduce this in MDX query. What I could achieve so far is FULL JOIN, while I need an INNER one. Any help would be appreciated. Test solution can be found here.

Answer 1

Stated another way, simply putting a multiselect in the where clause is an OR. You want an AND filter. Try this:

WITH
MEMBER [Measures].[HasBothProducts] as
IIf(
  ([Product].[Product ID].&[3], [Measures].[Order Ref Product Count]) > 0
 And  ([Product].[Product ID].&[4], [Measures].[Order Ref Product Count]) > 0
 ,1
 ,Null
)
SELECT 
 { [Measures].[Order Ref Product Count] } ON COLUMNS, 
NON EMPTY { ([Product].[Product ID].[Product ID].ALLMEMBERS ) } ON ROWS 
FROM [Sales]
WHERE NonEmpty([Order].[Order ID].[Order ID].Members, [Measures].[HasBothProducts])