StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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:</p> <pre><code>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] </code></pre> <p>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 <a href="http://sqlblog.com/blogs/mosha/archive/2005/06/30/how-to-check-if-cell-is-empty-in-mdx.aspx" rel="nofollow">blog post of the former lead developer of the MDX processor</a>, you can simplify the check for empty and null to just checking for zero.</p> <p><strong>EDIT:</strong></p> <p>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.</p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload