MDX showing values of only the best salesmen

Question

Let's say I have two simple dimensions:

Products - with id and name

Salesmen - with id and name

My fact table is named SALES and contains the ids of the abovementioned.

Let's say product X has been sold by salesmen A, B and C.

Product Y has been sold by salesmen B, C, and D

I want to produce a MDX query which would tell me the names of the salesmen who sold both those products. In this case the result would be B and C

My attempt:

select {null} on 0,
DESCENDANTS (
[Salesmen].[Name].children
) on 1
FROM [Test]
where (
{
       (
             [Products].[Name].&[X]
       )
,
       (
             [Products].[Name].&[Y]
       )
}
)

Answer 1

Please try nesting exist function like this:

SELECT 
  {} on 0,
  EXISTS(
    EXISTS(
       {[Salesmen].[Name].MEMBERS}, //<<TRY THIS INSTEAD
       {[Products].[Name].&[X]}
    )
    ,{[Products].[Name].&[Y]}
  )
  ON 1
FROM [Test];

Strictly speaking EXISTS requires the name of a measures group as it's third argument like this:

SELECT 
  {} on 0,
  EXISTS(
    EXISTS(
       {[Salesmen].[Name].MEMBERS} //<<TRY THIS INSTEAD
       ,{[Products].[Name].&[X]}
       ,"Reseller Sales" //<<replace with group name from your cube
    )
    ,{[Products].[Name].&[Y]}
    ,"Reseller Sales" //<<replace with group name from your cube
  )
  ON 1
FROM [Test];

An alternative approach is to use a member from the hierarchy [Measures] and the functions NonEmpty and Intersect:

SELECT 
  {} on 0,
  INTERSECT(
    NONEMPTY(
       {[Salesmen].[Name].MEMBERS}
       ,([Products].[Name].&[X],[Measures].[SomeMeasureInYourCube])
    )
   ,NONEMPTY(
       {[Salesmen].[Name].MEMBERS}
       ,([Products].[Name].&[Y],[Measures].[SomeMeasureInYourCube])
    )
  )
  ON 1
FROM [Test];

The above may well work with a simple tuple of the members of Products only

SELECT 
  {} on 0,
  INTERSECT(
    NONEMPTY(
       {[Salesmen].[Name].MEMBERS}
       ,([Products].[Name].&[X])
    )
   ,NONEMPTY(
       {[Salesmen].[Name].MEMBERS}
       ,([Products].[Name].&[Y])
    )
  )
  ON 1
FROM [Test];