How to filter this?

I Have 2 tables

Tables

Cat. ID

Category 1

2

Cat. Id Brand ID

Brand 1 129007

2 129999

And i had try to write the MDX as follow

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

But result is displayed all the result, How should i filter to only display Cat.ID =1 ?

1 2 3 4

VOP PPU CDC FOP

129007 90.8 99.9 78.9 77.3

129999 3.3 2.2 1.1 2.2 >This Rows should be filtered..

Thanks In advance

Hi tsohtan,

just add a WHERE-clause like the following:

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1])

Michael.

|||

Hi Michael,

Thanks for you answer.

How about filter more than one condition?

is that like this?

where condition1 & condition2?

Because i not sure is my data problem, it come out different result it should generate.

Thanks again.

|||

Hi tsohtan,

it depends on wether you want to filter by the same dimension but several members or by a member of another dimension.

Case 1: Filtering by more than one dimension

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE {[Dim Category].[Dim Category].&[1], [Dim Category].[Dim Category].&[2]}

Case 2: Filtering by a member of another dimension

Code Snippet

[Dim Category].[Dim Category].&[1]

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1], [Dim Other Dimension].[Dim Other Dimension].&[42])

You can also combine this. You can read in BOL about the MDX basics in the MDX Reference chapter. The basic elements of MDX (member, tuple and set) are explained there.

Michael.

|||

Hi Michael,

I not sure what is the "Where" mean here, a bit confuse.

Because i set the WHERE (Dim Category].[Dim Category].&1) and without the "WHERE" condition.

The total counts is same.

Any idea?

Thanks again

|||

Hi tsohtan,

maybe your Category dimension is not properly joined with your measure group?

Michael.

|||

yes you are right. and i get the right result.

thank you very much michael