The Intersect function returns the intersection two input sets, optionally retaining duplicates. Syntax:
Intersect (set, set [,ALL])
Use the Intersect function to compare sets by finding tuples that are present in both sets.
To use the Intersect function:
Delete the braces {} from the axis, and replace them with Intersect() . For example:
SELECT
Intersect (
)
ON COLUMNS
FROM Sample.Basic
Add two comma-separated pairs of braces to use as placeholders for the two set arguments you will provide to the Intersect function. For example:
SELECT
Intersect (
{ },
{ }
)
ON COLUMNS
FROM Sample.Basic
Specify children of East as the first set argument. For example:
SELECT
Intersect (
{ [East].children },
{ }
)
ON COLUMNS
FROM Sample.Basic
For the second set argument, specify all members of the Market dimension that have a UDA of “Major Market.” For example:
SELECT
Intersect (
{ [East].children },
{ UDA([Market], "Major Market") }
)
ON COLUMNS
FROM Sample.Basic
Paste the query into the MaxL Shell and run it, as described in Exercise: Running Your First Query.
Results: All children of East that have a UDA of “Major Market” are returned. For example:
New York Massachusetts Florida 8202 6172 5029