Using MDX Filter() to limit number of results

Question

Using MDX Filter() to limit number of results

adzymcfadzy

Right there with Babe

Points: 794
More actions
April 28, 2010 at 8:40 pm

#218340

Hi all,
What i want to do is look at sales of only certain transaction types in my cube. the trans codes are '001', '002', '144' etc
I was thinking i could use Filter() as a way to limit the sales information for the products.
eg (a very cut down table which the cube is based on)
ID TransCode TransValue
1 001 100
2 002 200
3 001 300
4 004 500
5 144 250
I have been trying to create a named set under calculations tab in my project to do this:
FILTER( [Measures].[Trans Value], [PVH TransCodes].[Trans Code] = {'001', '002', '144'})
Im clearly going about this the wrong way, im wondering if i am even close to being on track.
I've tried to read various sources on how to use filters, but not seeing how i can do what i want to do.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

cees van diermen SSC Eights! Points: 841 More actions · Answer 1

Hi adzymcfadzy,

MDX differs from TransactSql. Instead of using Filter I suggest you create your set by assigning specific members to the set. Using AdventureWorks something like this:

with

set [x] as

{[Customer].[Customer Geography].[Country].&[Australia],

[Customer].[Customer Geography].[Country].&[Canada],

[Customer].[Customer Geography].[Country].&[France]}

select [x] on 0,

[Measures].[Reseller Order Count] on 1

from [Adventure Works]

result is:

AustraliaCanadaFrance

Reseller Order Count5,5845,5845,584

Assuming that 001, 002 and 144 are the key of the dimensionmember keys, the set you need will be like this:

{[PVH TransCodes].[Trans Code],&[001],

[PVH TransCodes].[Trans Code],&[002],

[PVH TransCodes].[Trans Code],&[144]}

Hope that helps you out,

Cees

adzymcfadzy Right there with Babe Points: 794 More actions · Answer 2

Thanks for the reply,

MDX is certainly a different beast to TSQL 🙂 I have been doing more readng on it, thanks for putting me on the right track.

Regards

Adam