Returns the intersection of two sets, optionally retaining duplicates.

Union( {A,A,B,F,G}, {A,B,C} ) -> {A,B}
Union( {A,A,B,F,G}, {A,B,C},ALL ) -> {A,A,B}

The ALL flag specifies that the function should retain duplicates from the first set; when not specified, the function does not retain duplicates.

Examples

Intersect(set,set) - members

Returns all members present on both sets, in our case [Europe].

Query

INTERSECT(
    {[Geography].[Geo].[Europe],[Geography].[Geo].[America]}
    ,
    {[Geography].[Geo].[Europe]}
)

Result

{ 
  [Geography].[Geo].[Continent].[Europe]
}

Assertion : MDX Equals

{[Geography].[Geo].[Europe]}

Intersect(set,set) - tuples

The intersect function works also for tuples, returning tuples present in both sets.

Query

INTERSECT(
    [Product].[Prod].[Corporate] * {[Geography].[Geo].[Europe],[Geography].[Geo].[America]}
    ,
    [Product].[Prod].[Corporate] * {[Geography].[Geo].[Europe]}
)

Result

{
  ( [Product].[Licence].[Corporate], [Geography].[Geo].[Continent].[Europe] )
}

Assertion : MDX Equals

{([Product].[Prod].[Corporate],[Geography].[Geo].[Europe])}

Intersect(set,set,ALL)

Returns members present on both sets, keeping duplicates of the first set.

Query

INTERSECT(
    {[Geography].[Geo].[Europe],[Geography].[Geo].[Europe]},
    {[Geography].[Geo].[Europe]},
    ALL
)

Result

{ 
  [Geography].[Geo].[Continent].[Europe], 
  [Geography].[Geo].[Continent].[Europe]
}

Assertion : MDX Equals

{[Geography].[Geo].[Europe],[Geography].[Geo].[Europe]}

Assertion : MDX Equals

INTERSECT(
    {[Geography].[Geo].[Europe],[Geography].[Geo].[Europe]},
    {[Geography].[Geo].[Europe],[Geography].[Geo].[Europe],[Geography].[Geo].[Europe]},
    ALL
)

Intersect(set,set,ALL) - duplicated members in second set are ignored

The duplicated members in the second set are not retained in the intersection.

In our example, a single [Europe] member is returned.

Query

INTERSECT(
    {[Geography].[Geo].[Europe]},
    {[Geography].[Geo].[Europe],[Geography].[Geo].[Europe]},
    ALL
)

Result

{ 
  [Geography].[Geo].[Continent].[Europe]
}

Assertion : MDX Equals

{[Geography].[Geo].[Europe]}

Special Examples (errors, null, ...)

Intersect(set)

Applying intersect for a single set generates an error.

Query

INTERSECT( [Geography].[Geo].DefaultMember )

Result


Error Code    : OLAP_FUNCTION_UNEXPECTED_ARGUMENT_COUNT
Error Message : Intersect() : unexpected number of parameters '1' (expected:'2:3')

Intersect({},NULL)

Returns an empty set if applied to a NULL entity or empty sets.

Query

INTERSECT({},{})

Result

{}

Assertion : MDX Equals

INTERSECT({},null)

Assertion : MDX Equals

INTERSECT(null,{})

Assertion : MDX Equals

INTERSECT(null,null)

Intersect(set,set) - members - different dimensionality

Using sets with different dimensionalities causes an error.

Query

INTERSECT( [Geography].[Geo].members, [Geography].[Economy] )

Result


Error Code    : OLAP_INVALID_TUPLE_DIMENSIONALITY
Error Message : invalid tuple dimensionality '[Geography].[Geo]' - '[Geography].[Economy]'

Intersect(set,set) - tuples - different dimensionality

Using sets with different dimensionalities causes an error.

Query

INTERSECT(
    { ( [Geography].[Geo].[Spain], [Time].[Calendar].[2010]  ) },
    { ( [Time].[Calendar].[2010] , [Geography].[Geo].[Spain] ) }
)

Result


Error Code    : OLAP_INVALID_TUPLE_DIMENSIONALITY
Error Message : invalid tuple dimensionality '[Geography].[Geo],[Time].[Calendar]' - '[Time].[Calendar],[Geography].[Geo]'

Intersect(string,set)

Using an invalid parameter causes an error.

Query

INTERSECT("XXX",[Geography].[Geo].[Continent].Members)

Result


Error Code    : OLAP_FUNCTION_ARGUMENT_TYPE_MISMATCH
Error Message : Intersect() : argument (0) type mismatch: expected:'set', got:'string'

Intersect(set,set,XYZ)

An invalid flag generates an error.

Query

INTERSECT( {}, {}, XYZ)

Result


Error Code    : OLAP_FUNCTION_ARGUMENT_TYPE_MISMATCH
Error Message : Intersect() : argument (2) type mismatch: expected:'[ALL]', got:'function option(XYZ)'

XMLA Properties

Find here the value of the XMLA properties when not specified otherwise.

icCube OLAP version: 2.5.1 13-May-2012 02:21:10 GMT

Parameters

Return

Description

See Also

Examples

Special Examples (errors, null, ...)

XMLA Properties