Cubing — Intersect function (MDX)

The Intersect function returns a set that consists of the members that two specified sets have in common.

Returns: A set.

Syntax


>>-Intersect--(--SetExpression1--,--SetExpression2--+--------+--)-><
                                                    '-,--ALL-'

Parameters

SetExpression1: Specifies the set whose members, if they are also members of the second set, are to be returned.
SetExpression2: Specifies the set whose members, if they are also members of the first set, are to be returned.
ALL: Specifies that the function retain duplicates within the result set. If you do not specify ALL, the function excludes duplicate members from the result set.

Description

The Intersect function compares two sets and then returns a set that consists of the members that exist in both of the specified sets. For example, the intersection of the sets { [A],[B],[C],[D] } and { [A],[C],[E] } is the set { [A],[C] }

By default, the Intersect function eliminates duplicate members from the combined set. However, by specifying ALL, you can retain the duplicate members in the set that the function returns

Example

The following example uses the Intersect function to retrieve the sales amount for product departments with a name containing the words "GIRL" and "TEEN".

Query

SELECT {[Price Analysis].[Measures].[Sales Amount]} 

ON AXIS(0), 

{Intersect(Filter([Price Analysis].[Product].[All Products].Children, 

InStr([Price Analysis].[Product].CurrentMember.Name, "GIRL") > 0),

Filter([Price Analysis].[Product].[All Products].Children, 

InStr([Price Analysis].[Product].CurrentMember.Name, "TEEN") > 0))} 

ON AXIS(1)

FROM [Price Analysis]

Result

Product	Sales Amount
PETITE GIRL TEENS	19611.68
TEEN GIRLS	35005.8
TEEN GIRLS JEANS	52952.56

Related functions

You can perform basic set operations using the Union, Intersect, Except, and Crossjoin functions.