I've got an MDX query that's returning 2 different values, a total number of lookups and a number of lookups failed (and some other stuff, but that's the important part).
with
member [Pct Success] as 'iif(isempty([Num Lookup]) or [Num Lookup]=0, null,
100 * (coalesceempty([Num Lookup] - [Num Failed], 0) / [Num Lookup]))'
select
{
[Measures].[Pct Success],
[Measures].[Num Lookup],
[Measures].[Num Failed]
} on 0,
[Calendar].[Date].children on 1
from
[Cube]
Now what I'm trying to do is to get another success measurement, but I want this one to suppress any [Num Failed] cells that are below a specific threshold. Specifically, if I have at least 4 successful lookups (Num Lookup - Num Failed > 3 && Num Lookup > 4), then I want to make that cell's [Num Failed] = 0 (or rather I want to make a copy of [Num Failed] = 0 since I still need to display the original % Success measure).
The underlying facts look like this (just the pertinent subset of columns - the Line column is there for ease of reference, it's not in the actual facts):
Line | CalendarKey | Num Failed | Num Lookup 1 | 20130601 | 2 | 8 2 | 20130601 | 5 | 8 3 | 20130601 | 1 | 8 4 | 20130601 | 0 | 7 5 | 20130601 | 7 | 8 6 | 20130602 | 2 | 6 7 | 20130602 | 1 | 7 8 | 20130602 | 5 | 10 9 | 20130602 | 7 | 9 etc.
What I expect to see for results based on those facts above is:
| % Success | % Filt Success | Num Filt Failed | Num Failed | Num Lookup
20130601 | 61.53 | 69.23 | 12 | 15 | 39
20130602 | 53.13 | 71.88 | 9 | 15 | 32
In the above fact sample, lines 1, 3, 4, 7 & 8 all would have a filtered failed value of 0, which gives us the sample output listed above.
My initial thought is to use another member in the with clause as the copy of [Num Failed] and then a Cell Calculation to do the suppression, but I can't get the Cell Calculation to work correctly - it either doesn't modify the value, or it errors out during evaluation.
Here's the non-working version that "should" return what I'm looking for, but doesn't:
with
member [Measures].[Num Threshold Failure] AS [Num Failed]
Cell Calculation [Data Filter] For '[Measures].[Num Threshold Failure]' AS 'NULL', Condition = 'CalculationPassValue((([Measures].[Num Lookup] - [Measures].[Num Failure]) < 4) AND ([Measures].[Num Lookup] > 4), 1)'
member [Pct Success] as 'iif(isempty([Num Lookup]) or [Num Lookup]=0, null, 100 * (coalesceempty([Num Lookup] - [Num Failed], 0) / [Num Lookup]))'
member [Pct Filtered Success] as 'iif(isempty([Num Lookup]) or [Num Lookup]=0, null, 100 * (coalesceempty([Num Lookup] - [Num Threshold Failure], 0) / [Num Lookup]))'
select
{
[Measures].[Pct Success],
[Measures].[Pct Filtered Success],
[Measures].[Num Threshold Failure],
[Measures].[Num Failed],
[Measures].[Num Lookup]
} on 0,
{ [Calendar].[Date].children } on 1
from
[Cube]
I do not understand your question in every detail, but as far as I understand it, the following should answer it, or at least come close:
with
member [Pct Success] as iif([Measures].[Num Lookup]=0,
null,
100 * (coalesceempty([Measures].[Num Lookup] -[Measures]. [Num Failed], 0) / [Measures].[Num Lookup]))
member [Filtered Failed] as iif([Measures].[Num Lookup] - [Measures].[Num Failed] > 3 and [Measures].[Num Lookup] > 4),
0,
[Measures].[Num Failed])
member [Bottom Filtered failed] as Sum(Leaves(),
iif([Measures].[Num Lookup] - [Measures].[Num Failed] > 3 and [Measures].[Num Lookup] > 4),
0,
[Measures].[Num Failed]))
member [Pct Filtered Success] as iif([Measures].[Num Lookup]=0,
null,
100 * (coalesceempty([Measures].[Num Lookup] -[Measures]. [Filtered Failed], 0) / [Measures].[Num Lookup]))
select
{
[Measures].[Pct Success],
[Measures].[Num Lookup],
[Measures].[Num Failed],
[Measures].[Num Filtered Failed],
[Measures].[Bottom Filtered Failed],
[Measures].[Pct Filtered Success]
} on 0,
[Calendar].[Date].children on 1
from [Cube]
BTW: You do not need to include member definitions in the WITH clause in single quotes unless you aim for the SQL Server 2000 dialect of MDX. And, according to this blog post of the former lead developer of the MDX processor, you can simplify the check for empty and null to just checking for zero.
EDIT:
As you stated that users want to use several different tolerances in what-if analyses, what you could do if your cube is not huge, and the number of different tolerances is just a handful, you could pre-calculate the what-if cases, thus making use of the fast response times of Analysis Services for aggregated values.
To do this, you would proceed as follows: build a small dimension table , say dim_tolerance, containing e. g. the numbers 0 to 10, or the numbers 0, 1, 2, 3, 5, 8, 10, and 12, or whatever makes sense. Then build a new fact table referencing the same dimensions as the current one, plus the new one, and fill it with the single measure [num failed filtered] calculated as the dim_tolerance value dictates. You could then remove the [num failed] measure from the main fact table (as it would be the same as [num failed filtered] with tolerance 0). Make the attribute in the new dimension non-aggregateable with a default value of 0.