none
Creating a Calculated measure based on other Calculated measures RRS feed

  • Question

  • Hi,

    I need help creating a calculated measure. I have two calculated measures A and B. They are just counts. I need to create a new calculated measure C that depends on A and B. Measure C is when A or B is 1 then C =1 and if A and B = 0 then C = 0(See the below). So I created a case statement

    A | B | C

    1   1   1

    1   0   1

    0   1   1

    0   0   0 

    case when A <> null then A

              when B <> null then B

              else null

    end

    This works well at the leaf or Children level but does not work well at the parent level. The value that gets display is the value of Measure A.

    So it display 2 instead of 3 (this is based on the above table).

    I also tried using Count(Filter( dimension.members, A <> null or B <> null)) . This display the current value 3 for all the empty members and non empty members.

    Please does anyone have any Idea?

    Thanks,

    Femi





    femi

    Friday, March 30, 2012 4:45 PM

All replies

  • MDX treats NULLs as zeros so in your case, the null check isn't even needed. Just define [Measures].[C] = [Measures].[A] + [Measures].[B] Based on the table above, [C] would equal 3.

    HTH, Martin


    http://martinmason.wordpress.com

    Saturday, March 31, 2012 12:08 PM
  • Hi Martin

    Thanks for your response. Let me rephrase my questions. Assuming I have a list of customers that bought a either product A or Product B or both (meaning customer bought Product A and Product B). see table below. So Measure A is a count  of customer that bought Product A and measure B is a count of those customer that bought product B. Measure C will be a count of customer that bought Product A or Product B or even both. The current solution that I have display the current count 5 for Measure C but when I view with my customer dimension it display 5 for all the customers in my customers in my customer dimension.The solution is Count(Filter( dimension.members, A <> null or B <> null))

    So adding both Measure A and Measure B together provides the list of customer that bought A and B but if it will not give the correct count of 5.

    Any ideas

    Thanks,

    Femi


    femi

    Wednesday, April 4, 2012 8:39 PM
  • hi,

    indeed, your calculated measure will not work for any aggregation of A and B. you could define c as a named calculation in dsv with your formula find in your original question. this named calculation will withstand aggregation and any subset definition of customer.

    Philip,

    Wednesday, April 4, 2012 9:47 PM
  • Hi Femi,

    Thanks for clarifying. Try changing the definition of your measure slightly and instead of using a Filter/Count combination which will require cell-by-cell computation, try using an approach employing a SUM/IIf which will be evaluated in block mode. Let me know if the following definition works. Also, with aggregations over sets where the context changes, you're likely going to have to use the EXISTING declaration before the set so that it's evaluated using the current query context.

    WITH MEMBER [Measures].[Sum C] AS
    	IIf(
    		[Measures].[A] <> 0 OR [Measures].[B] <> 0,
    		1,
    		NULL
    	)
    MEMBER [Measures].[C] AS
    	SUM(
    		EXISTING [Dimension].Members,
    		[Measures].[Sum C]
    	) 

    HTH, Martin 


    http://martinmason.wordpress.com

    Thursday, April 5, 2012 12:40 PM