The Intersect functionality is of the three Venn Collection functionality's (EXCEPT, INTERSECT, UNION).
Intersect will give all the members of SET1 which are also in SET2. By default it will remove any duplicates.
SELECT
INTERSECT (
{
[Date].[Calendar Year].&[2010]
,[Date].[Calendar Year].&[2012]
,[Date].[Calendar Year].&[2013]
,[Date].[Calendar Year].&[2013]
} //SET 1
,
{
[Date].[Calendar Year].&[2012]
,[Date].[Calendar Year].&[2013]
,[Date].[Calendar Year].&[2013]
,[Date].[Calendar Year].&[2014]
} //SET 2
) ON COLUMNS,
[Measures].[Sales Amount] on ROWS
FROM
[Adventure Works]
Will get you:
(Only [CY 2012] and [CY2013] are in both sets)
If we add the ALL flag duplicates will be visible:
SELECT
INTERSECT
(
{
[Date].[Calendar Year].&[2010]
,[Date].[Calendar Year].&[2012]
,[Date].[Calendar Year].&[2013]
,[Date].[Calendar Year].&[2013]
} //SET 1
,
{
[Date].[Calendar Year].&[2012]
,[Date].[Calendar Year].&[2013]
,[Date].[Calendar Year].&[2013]
,[Date].[Calendar Year].&[2014]
} //SET 2
, ALL
) ON COLUMNS,
[Measures].[Sales Amount] on ROWS
FROM
[Adventure Works]
([CY2013] is a duplicate, it doesn’t matter in which set it’s a duplicate.)
Have Fun And Till Next Time
The best live casino sites | Lucky Club
ReplyDeleteLucky Club has the best live dealer casino. Get all you need to know about online casino games and bonuses available luckyclub at the best online casinos in the UK.