# Get Intersection of two MDX Qureies

### Question

• Hi All,

I have two different MDX queries and I want to get the common result.

```With
Set [Set1] AS FILTER ( NONEMPTY( [Account].[Account].[All].CHILDREN), [Measures].[Amount] >= [Measures].[Amount] )
Set [Set2] AS FILTER ( NONEMPTY( [Account].[Account].[All].CHILDREN), [Measures].[Amount] >= 0 )
Set [Set3] AS FILTER ( NONEMPTY( [Account].[Account].[All].CHILDREN), [Measures].[Amount] >= 0 )
SELECT

{[Measures].DefaultMember} ON 0,
INTERSECT(INTERSECT([Set1],[Set2]),[Set3]) ON 1
FROM
[Dynamics NAV]```

And second one is:

```SELECT
{[Measures].DefaultMember} ON 0,
{[Account].[Account].[All].CHILDREN}  ON 1
FROM
[Dynamics NAV]
WHERE
(
{FILTER([Account].[Chart of Accounts].MEMBERS,Left([Account].[Chart of Accounts].CURRENTMEMBER.Properties("Caption"), 1) = "B")}
);```

I want to Intersect their results. Any ideas?

Thanks,

Attiqe

Attiqe Ur Rehman

Saturday, December 21, 2013 10:30 AM

• Just a suggestion but if there is anyway possible to avoid using a Filter function, do so. The Filter function is evaluated in cell-by-cell mode and will almost always perform very poorly. You can more efficiently write your function above using a much more efficient approach by using Nonempty and a calculated measure. Applying OR conditions using UNION is a bit more difficult than Intersect.

```WITH MEMBER [Measures].[Filter Conditions] AS
IIf(
[Measures].[Amount] > 0 AND
Left([Account].[Account].CurrentMember.Properties("Chart Of Accounts", TYPED), 1) = "B",
1,
NULL
)
SELECT	{
[Measures].DefaultMember
} ON COLUMNS,
NonEmpty(
[Account].[Account].[All].Children,
[Measures].[Filter Conditions]
) ON ROWS
FROM	[Dynamics NAV]
```

HTH, Martin

&lt;a href=&quot;http://martinsbiblog.spaces.live.com&quot; target=&quot;_blank&quot;&gt;http://martinmason.wordpress.com&lt;/a&gt;

Monday, December 23, 2013 10:30 AM

### All replies

• Hi Attiqe,

According to your description, you want to join the results of two MDX queries together, right? Currently, this functionality isn’t supported in Analysis Services MDX. However, there is a workaround for this requirement, for the detail information about it, please refer to the link below to see the blog.
Joining the results of two MDX queries together

Regards,

Charlie Liao
TechNet Community Support

Monday, December 23, 2013 9:07 AM
• Just a suggestion but if there is anyway possible to avoid using a Filter function, do so. The Filter function is evaluated in cell-by-cell mode and will almost always perform very poorly. You can more efficiently write your function above using a much more efficient approach by using Nonempty and a calculated measure. Applying OR conditions using UNION is a bit more difficult than Intersect.

```WITH MEMBER [Measures].[Filter Conditions] AS
IIf(
[Measures].[Amount] > 0 AND
Left([Account].[Account].CurrentMember.Properties("Chart Of Accounts", TYPED), 1) = "B",
1,
NULL
)
SELECT	{
[Measures].DefaultMember
} ON COLUMNS,
NonEmpty(
[Account].[Account].[All].Children,
[Measures].[Filter Conditions]
) ON ROWS
FROM	[Dynamics NAV]
```

HTH, Martin

&lt;a href=&quot;http://martinsbiblog.spaces.live.com&quot; target=&quot;_blank&quot;&gt;http://martinmason.wordpress.com&lt;/a&gt;

Monday, December 23, 2013 10:30 AM
• Hi Attiqe,

According to your description, you want to join the results of two MDX queries together, right? Currently, this functionality isn’t supported in Analysis Services MDX. However, there is a workaround for this requirement, for the detail information about it, please refer to the link below to see the blog.
Joining the results of two MDX queries together

Regards,

Charlie Liao
TechNet Community Support

This doesn't really address the question being asked. Chris Webb's article addresses a situation where members from two different attribute hierarchies need to be combined so that they appear as if all members are associated with the same hierarchy.

&lt;a href=&quot;http://martinsbiblog.spaces.live.com&quot; target=&quot;_blank&quot;&gt;http://martinmason.wordpress.com&lt;/a&gt;

Monday, December 23, 2013 10:36 AM
• Hi Martin,

Thank you for pointing it out.

Regards,

Charlie Liao
TechNet Community Support

Monday, December 23, 2013 12:27 PM