none
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

Answers

  • 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


    <a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>

    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


    <a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>

    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.

    <a href="http://martinsbiblog.spaces.live.com" target="_blank">http://martinmason.wordpress.com</a>

    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