Fillter Function In MDX Query Not Work?

Question

I use SQL Server 2008 R2 .

I Use SSAS and MDX Query for select data from Adventure Works Data warehouse .

And i need get data from two measure [Measures].[Internet Sales Amount] and [Measures].[Internet Tax Amount] where values in [Measures].[Internet Sales Amount] measure is greater than 2644017.71 .

But my Query not work ?

Select
Filter(
        {
            [Measures].[Internet Sales Amount]
            ,[Measures].[Internet Tax Amount]
        } , 
        [Measures].[Internet Sales Amount] >= 2644017.71
      ) on columns,
head(
        [Customer].[Customer Geography].[Country],
        3
    )on rows
From [Adventure Works]

enter image description here

FrankPl FrankPl · Accepted Answer · 2013-08-19T17:23:48

You should use

Select {
            [Measures].[Internet Sales Amount]
            ,[Measures].[Internet Tax Amount]
        } 
on columns,
head(
   Filter(
        [Customer].[Customer Geography].[Country] , 
        [Measures].[Internet Sales Amount] >= 2644017.71
      ),
   3) 
on rows
From [Adventure Works]

as you want to filter the rows (i. e. the countries), not the columns (the measures).

Just to be sure: This code delivers the first three countries in the order in which the Country attribute is configured in cube design (normally alphabetically). If this is not what you want, you might want to use use TopCount() instead of Head(), which delivers the three countries with the highest sales:

Select {
            [Measures].[Internet Sales Amount]
            ,[Measures].[Internet Tax Amount]
        } 
on columns,
TopCount(
    Filter(
        [Customer].[Customer Geography].[Country] , 
        [Measures].[Internet Sales Amount] >= 2644017.71
        ),
   3, [Measures].[Internet Sales Amount]) 
on rows
From [Adventure Works]

Fillter Function In MDX Query Not Work?

1 Answers