none
scope: what's the context for the measure in the right part? RRS feed

  • Question

  • I tested 4 scoped expression, and the result are the same. So I am wonder how the attribute hierarchy is set when it isn't mentioned in the RIGHT part of the measure, such my example, they are feedback & [Alert Count]. Since in the left part, I used [Store].[Store].[Store].members, [Calendar].[Date].[Date].members, so can I say it will apply ([Store].[Store].currentmember,[Calendar].[Date].currentmember) to every measures mentioned in the right part? meaning, SSAS will apply ([Store].[Store].currentmember,[Calendar].[Date].currentmember) to both feedback & [Alert Count]?

    select {[Measures].[test1],
            [Measures].[test2],
            [Measures].[test3],
            [Measures].[test4]} on 0,
           non empty [CALENDAR].[Date].members on 1
    from test

    ([Store].[Store].[Store].members*[Calendar].[Date].[Date].members
    ,[measures].[test1]) = IIF(([Store].[Store].currentmember
    ,[Calendar].[Date].currentmember,feedback) > 0,[Alert Count],null );

    ([Store].[Store].[Store].members*[Calendar].[Date].[Date].members
    ,[measures].[test2]) = IIF(feedback> 0,[Alert Count],null );

    ([Store].[Store].[Store].members*[Calendar].[Date].[Date].members
    ,[measures].[test3]) = IIF(feedback> 0,([Store].[Store].currentmember,[Calendar].[Date].currentmember,[Alert Count]),null );

    ([Store].[Store].[Store].members*[Calendar].[Date].[Date].members
    ,[measures].[test4]) = IIF(feedback> 0,([Store].[Store].[ALL],[Calendar].[Date].[ALL],[Alert Count]),null );

    Monday, October 17, 2011 5:48 AM

Answers

  • I'm not sure I know exactly what you're after, but I think you might want to get a measure Alert Count for all stores and dates in which another measure Feedback is greater than zero.

    Below is a similar example from Adventure Works. I'm returning Internet Gross Profit for all product categories and dates in which Internet Sales Amount is greater than $25,000.

    SELECT
    	{
    		[Measures].[Internet Gross Profit]
    	} ON 0
    ,	NON EMPTY {
    		Filter (
    			[Product].[Category].Children
    		*	[Date].[Date].Children
    		,	[Measures].[Internet Sales Amount] > 25000
    		)
    	} ON 1
    FROM
    	[Adventure Works]
    

    So for your example it would be something like...

    SELECT
    	{
    		[Measures].[Alert Count]
    	} ON 0
    ,	NON EMPTY {
    		Filter (
    			[Store].[Store].[Store].Children
    		*	[Calendar].[Date].[Date].Children
    		,	[Measures].[Feedback] > 0
    		)
    	} ON 1
    FROM
    	[Test]
    



    David Stewart | My Blog | Follow me on Twitter
    • Marked as answer by Daniel Wu Tuesday, October 18, 2011 8:07 AM
    Monday, October 17, 2011 6:11 AM