I've scoured several boards and even posted this on another with no response...but have not been able to find this exact question or unable to get responses to somewhat similar questions to work because MDX is my achilles heel. I want to sum/count a value only if the brand attributes from the 2 dimensions below are equal.

[Placement].[Placement Brand]
A
B
C

[Metric].[Brand]
A
B
C

[Measures].[Value]

What I basically want to create is a calculated measure that looks like this, but I know the tuple in my sum() is what is causing the problem and not sure how to fix it or limit it to that brand that satisfies the = in the logical expression.

IIF([Placement].[Placement Brand].MEMBERVALUE = [Metric].[Brand].MEMBERVALUE, SUM([Metric].[Brand],[MEASURES].VALUE), 0)

The other piece to this I want this to be able to work even if I'm not using the [Placement] dimension...every record in my fact table has a placement key and metric key...is that even possible?

I have a CASE statement doing what I want, but then I have to hard code the brand values in it AND it requires the use of the placement brand.

CASE [Placement].[Placement Brand].MEMBERVALUE
    WHEN 'A' THEN SUM([Metric].[Brand].&[A], [Measures].[Value])
    WHEN 'B' THEN SUM([Metric].[Brand].&[B], [Measures].[Value])
    WHEN 'C' THEN SUM([Metric].[Brand].&[C], [Measures].[Value])
    WHEN 'D' THEN SUM([Metric].[Brand].&[D], [Measures].[Value])
   ELSE 0
END

Thanks for any help!

Hi,

have u thought of moving this calculation to the underlying datasource?

Hi,

Please try the below expression for the measure

IIF([Placement].[Placement Brand].CURRENTMEMBER.MEMBERVALUE = [Metric].[Brand].CURRENTMEMBER.MEMBERVALUE, SUM([Metric].[Brand],[MEASURES].VALUE), 0)

Hi ravindra_k,

According to your description, you have two dimensions have same member key, now you want to get the sum of [Metric].[Brand] when slicing on [Placement].[Placement Brand] in your MDX query. Right?

In this scenario, since these two dimensions has same member key, you can get the member key of [Placement].[Placement Brand] first, then use STRTOMEMBER() to concatenate the [Metric].[Brand] with the member key and convert it into a member. Please refer to sample query below:

with member [x] as 
 sum(strtomember('[Date].[Fiscal].[Date].&['+[Date].[Calendar].currentmember.member_key+']'),
 [Measures].[Internet Sales Amount])
 
 select [x] on 0,
 [Date].[Calendar].[Date].members on 1
 from 
 [Adventure Works]

Regards,

Thanks Simon. Sorry for the delay I've been traveling a lot and then on vacation.

The underlying fact table has Placement and Metric, so I'd like to compare the brand attributes associated with those dimensions, but not necessarily be forced to have Placement in the slicer when viewing the cube in a pivot. Not sure if that makes sense or is possible.

This might be headed down the right path but I'm not sure quite what I'm looking for.

Thanks Sorna, but this still forces me to have Placement Brand active in the pivot table when I connect to the cube. I need it be independent of current member. Again, not sure if what I'm trying to do is possible but it seems like I should be able to since both fields (placement and metric) are present for every record in the fact table.