Intersect

The MDX Intersect function for Essbase returns the intersection of two sets. Duplicates are eliminated by default from the tail of the set. The optional ALL keyword retains duplicates. The two input sets must have identical dimension signatures: If set1 consists of dimensions Product and Market, in that order, then set2 must also.

Syntax

Intersect ( set1, set2 [,ALL] )

Parameters

set1: A set to intersect with set2.
set2: A set to intersect with set1.
ALL: The optional ALL keyword retains matching duplicates in set1 and set2.

Example 1

The following expression:

Intersect({[New York], [California], [Oregon]}, 
          {[California], [Washington], [Oregon]})

returns the set:

{[California], [Oregon]}

Therefore, the following query:

SELECT
Intersect({[New York], [California], [Oregon]}, 
          {[California], [Washington], [Oregon]})
ON COLUMNS
FROM Sample.Basic

returns the grid:

Table 4-84 Output Grid from MDX Example

California	Oregon
12964	5062

Example 2

The following expression:

Intersect( { [New York], [California], [Florida], [California] },
           { [Oregon], [Washington], [California], [Florida], [California] }, ALL)

returns the set:

{ [California], [Florida], [California] }

Therefore, the following query:

SELECT
Intersect( { [New York], [California], [Florida], [California] },
           { [Oregon], [Washington], [California], [Florida], [California] }, ALL)
ON COLUMNS
FROM Sample.Basic

returns the grid:

Table 4-85 Output Grid from MDX Example

California	Florida	California
12964	5029	12964

The matching duplicate element [California] is duplicated in the result.

However, the following expression:

Intersect( { [New York], [California], [Florida], [California] },
           { [Oregon], [Washington], [California], [Florida] }, ALL)

would return only { [California], [Florida] }, because only one match exists between [California] in set1 and [California] in set2.