July 27, 2010 at 7:29 am
Hi all - can anyone help me with the correct syntax to do the following.
I need to filter my cost value and sales value as new measure in BIDS so that the volumes stay as is
I have 3 codes that I need to use as a filter
sam1
sam2
sam3
I need to filter cost and sales value as a new measure not including thse 3 codes
somethin like below
measure name - SALES EX SAM
FILTER (
[[Measures].[Sales ].MEMBERS,
([Customer].[Customer Code]) <> 'sam1','sam2','sam3')
And would repeat tis for the cost - I just don't know what the correct syntax to acheive this in MDX would be
Anyoe able to help ?
July 29, 2010 at 3:07 am
Hi,
you could either created a calculated measure (as you requested)
with
set Sam as {
[Customer].[Customer Code].[Sam1],
[Customer].[Customer Code].[Sam2],
[Customer].[Customer Code].[Sam3]
}
member [measures].[sales ex sam] as
sum(
except(
[Customer].[Customer Code].members,
Sam
),
[Measures].[sales]
)
select {[measures].[sales ex sam]} on columns
from [your cube];
or do a simple select on your sales measures excluding customers with a sam code
with
set Sam as {
[Customer].[Customer Code].[Sam1],
[Customer].[Customer Code].[Sam2],
[Customer].[Customer Code].[Sam3]
}
select {[Measures].[sales]} on columns
from [your cube]
where
except(
[Customer].[Customer Code].members,
Sam
);
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply