none
MDX - NonEmpty function on multiple measures simultaneously

    Question

  • Hello all

    I was wondering what would the best syntax to return only the rows where at least one measure is nonempty:

    1)

    NonEmpty([DIM1].[H1].[LEVEL1].Members, {[Measures].[M1], [Measures].[M2], ..., [Measures].[Mn]}

    2)

    With member [Measures].[NonEmptyCheck] as [Measures].[M1] + [Measures].[M2] + ... + [Measures].[Mn]

    NonEmpty([DIM1].[H1].[LEVEL1].Members, {[Measures].[NonEmptyCheck]}

    Note: the aggregation function of all these measures is SUM

    Regards

    Stefan

    Tuesday, July 12, 2011 8:23 AM

Answers

  • Hi Stefan,

    I'm sorry I must have missinterpreted your requirements because I originally did that and then for some reason thought you needed an logical AND not OR behavior.

    In that case just use NonEmpty( <set1>, <set2>) where <set2> are your "OR" measures.

    Here is the query modified for the sample, and there is a commented assignment to allow the scenario. I've also left the nonemptycheck measure so you can test and see that the results are the same and how NE behavior comes handy in that case. 

    with
    set NE_measures as
    {
    	[Measures].[Internet Sales Amount],
    	[Measures].[Internet Standard Product Cost],
    	[Measures].[Internet Tax Amount],
    	[Measures].[Reseller Sales Amount]
    } 
    
    // Uncomment to create a scenario where Internet Sales Measures are empty
    /*
    cell calculation [empty Internet Sales measures] for 
    '	(
    		Head(Descendants([Date].[Calendar].[Calendar Year].&[2008], 3), 6)
    		,{
    			[Measures].[Internet Sales Amount],
    			[Measures].[Internet Standard Product Cost],
    			[Measures].[Internet Tax Amount]
    		}
    	)
    '
    AS NULL
    */
    
    
    member m_NonEmptyCheck as True
    ,NON_EMPTY_BEHAVIOR = 
    (
    	{
    		[Measures].[Internet Sales Amount],
    		[Measures].[Internet Standard Product Cost],
    		[Measures].[Internet Tax Amount],
    		[Measures].[Reseller Sales Amount]
    	}
    )
    
    select
    NE_measures on 0,
    NonEmpty
    (
    	Descendants([Date].[Calendar].[Calendar Year].&[2008], 3)
    	,NE_measures -- OR m_NonEmptyCheck
    )
     on 1
    from
    [Adventure Works]
    
    

    Regarding NON_EMPTY_BEHAVIOR being not recommended (and I'm using R2) - I don't think you should blindly follow that recommendation as I have seen a few cases where it helps the engine and speeds things up. I have an example here http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/9cf3012d-12eb-4686-9c7b-3a78b154b0ae/

    Regards,

    Hrvoje Piasevoli

    Wednesday, July 13, 2011 7:02 AM

All replies

  • HI Stefan,

    Don't know if there's a better way but here is my take on it:

    with
    set NE_measures as
    {
    	[Measures].[Internet Sales Amount],
    	[Measures].[Internet Standard Product Cost],
    	[Measures].[Internet Tax Amount],
    	[Measures].[Reseller Sales Amount]
    } 
    
    member m_NonEmptyCheck as 
    Generate
    (
    	NE_measures
    	,{Iif([Measures].CurrentMember, [Measures].CurrentMember, NULL)}
    ).Count = NE_measures.Count
    ,NON_EMPTY_BEHAVIOR = 
    (
    	{
    		[Measures].[Internet Sales Amount],
    		[Measures].[Internet Standard Product Cost],
    		[Measures].[Internet Tax Amount],
    		[Measures].[Reseller Sales Amount]
    	}
    )
    
    select
    NE_measures on 0,
    Filter
    (
    	[Date].[Calendar].[Month].&[2008]&[1].CHILDREN
    	,m_NonEmptyCheck
    )
     on 1
    from
    [Adventure Works]
    	
    
    


    HTH,

    Hrvoje Piasevoli

    Tuesday, July 12, 2011 9:00 AM
  • Hello Hrvoje

    Thank you for your response.

    My goal is to show a GL account as soon as one of the scenarios is non empty. With your code an account would only be shown if all the scenarios are non empty, right?

    I am also wondering why you are using the NON_EMPTY_BEHAVIOR property since from version 2008 of SSAS it is not recommanded anymore to use it.

    Wednesday, July 13, 2011 5:27 AM
  • Hi Stefan,

    I'm sorry I must have missinterpreted your requirements because I originally did that and then for some reason thought you needed an logical AND not OR behavior.

    In that case just use NonEmpty( <set1>, <set2>) where <set2> are your "OR" measures.

    Here is the query modified for the sample, and there is a commented assignment to allow the scenario. I've also left the nonemptycheck measure so you can test and see that the results are the same and how NE behavior comes handy in that case. 

    with
    set NE_measures as
    {
    	[Measures].[Internet Sales Amount],
    	[Measures].[Internet Standard Product Cost],
    	[Measures].[Internet Tax Amount],
    	[Measures].[Reseller Sales Amount]
    } 
    
    // Uncomment to create a scenario where Internet Sales Measures are empty
    /*
    cell calculation [empty Internet Sales measures] for 
    '	(
    		Head(Descendants([Date].[Calendar].[Calendar Year].&[2008], 3), 6)
    		,{
    			[Measures].[Internet Sales Amount],
    			[Measures].[Internet Standard Product Cost],
    			[Measures].[Internet Tax Amount]
    		}
    	)
    '
    AS NULL
    */
    
    
    member m_NonEmptyCheck as True
    ,NON_EMPTY_BEHAVIOR = 
    (
    	{
    		[Measures].[Internet Sales Amount],
    		[Measures].[Internet Standard Product Cost],
    		[Measures].[Internet Tax Amount],
    		[Measures].[Reseller Sales Amount]
    	}
    )
    
    select
    NE_measures on 0,
    NonEmpty
    (
    	Descendants([Date].[Calendar].[Calendar Year].&[2008], 3)
    	,NE_measures -- OR m_NonEmptyCheck
    )
     on 1
    from
    [Adventure Works]
    
    

    Regarding NON_EMPTY_BEHAVIOR being not recommended (and I'm using R2) - I don't think you should blindly follow that recommendation as I have seen a few cases where it helps the engine and speeds things up. I have an example here http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/9cf3012d-12eb-4686-9c7b-3a78b154b0ae/

    Regards,

    Hrvoje Piasevoli

    Wednesday, July 13, 2011 7:02 AM