Using MDX Filter() to limit number of results
-
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.
-
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
-
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply