Exists (MDX)
Returns the set of tuples of the first set specified that exist with one or more tuples of the second set specified. This function performs manually what auto exists performs automatically. For more information about auto exists, see Key Concepts in MDX (Analysis Services).
If the optional <Measure Group Name> is provided, the function returns tuples that exist with one or more tuples from the second set and those tuples that have associated rows in the fact table of the specified measure group.
Syntax
Exists( Set_Expression1 , Set_Expression2 [, MeasureGroupName] )
Arguments
Set_Expression1
A valid Multidimensional Expressions (MDX) expression that returns a set.Set_Expression2
A valid Multidimensional Expressions (MDX) expression that returns a set.MeasureGroupName
A valid string expression specifying a measure group name.
Remarks
Measure group rows with measures containing null values contribute to Exists when the MeasureGroupName argument is specified. This is the difference between this form of Exists and the Nonempty function: if the NullProcessing property of these measures is set to Preserve, this means the measures will show Null values when queries are run against that part of the cube; NonEmpty will always remove tuples from a set that that have Null measure values, whereas Exists with the MeasureGroupName argument will not filter tuples that have associated measure group rows, even if the measure values are Null.
Examples
Customers who live in California:
SELECT [Measures].[Internet Sales Amount] ON 0,
EXISTS(
[Customer].[Customer].[Customer].MEMBERS
, {[Customer].[State-Province].&[CA]&[US]}
) ON 1
FROM [Adventure Works]
Customers who live in California with sales:
SELECT [Measures].[Internet Sales Amount] ON 0,
EXISTS(
[Customer].[Customer].[Customer].MEMBERS
, {[Customer].[State-Province].&[CA]&[US]}
, "Internet Sales") ON 1
FROM [Adventure Works]
Customers with sales:
SELECT [Measures].[Internet Sales Amount] ON 0,
EXISTS(
[Customer].[Customer].[Customer].MEMBERS
, , "Internet Sales") ON 1
FROM [Adventure Works]
Customers whom bought Bikes:
SELECT [Measures].[Internet Sales Amount] ON 0,
EXISTS(
[Customer].[Customer].[Customer].MEMBERS
, {[Product].[Product Categories].[Category].&[1]}
, "Internet Sales") ON 1
FROM [Adventure Works]