Asked by:
Creating a Calculated measure based on other Calculated measures
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
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
 Proposed as answer by Jerry NeeModerator Monday, April 2, 2012 7:52 AM

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

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,

Hi Femi,
Thanks for clarifying. Try changing the definition of your measure slightly and instead of using a Filter/Count combination which will require cellbycell 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