Post reply

Calculated Measure Comparing 2 Dimension Values

  • June 16, 2015 at 5:35 pm

    #309283

    I've scoured several boards but have not been able to find this exact question or unable to get responses to similar questions to work because I struggle with MDX. I want to sum/count a value only if the brand values 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 expresions.

    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...is that even possible?

    Thanks for any help!

  • July 2, 2015 at 12:45 pm

    #1810034

    Think of the calculated measure as a pseudo-cursor. When iterating, it needs to use the member it is currently on (in the iteration) for the comparison to work. Try to introduce the "currentmember" function to achieve this, similar to the following:

    [Placement].[Placement Brand].currentmember.MEMBERVALUE = [Metric].[Brand].currentmember.MEMBERVALUE

    If you want this to work even if you're not using the dimension on an axis of your query, you'll need to step down to the lowest level by using the descendants() function. Give that a try and post your results if you don't mind.

  • July 7, 2015 at 10:57 am

    #1810765

    Thanks Martin. It is helpful to think of a calc measure in that way.

    The following formula seems to work the same with or without currentmember in there...and is doing what I want, but requires both dimension on either axis of the query.

    IIF([Placement].[Placement Brand].MEMBERVALUE = [Metric].[Brand].MEMBERVALUE,SUM([Measures].[Value]), 0)

    So I guess the second part of the problem is the harder part...getting it to work without requiring either of those dimensions on either axis. I'll play around with descendents() and report back.

    Thanks again.

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

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