Where condition in calculated member in OLAP cube

Question

I am new to MDX and want to develop SSAS cube and need some help on following scenario :

In the SSAS designer, on the Calculations tab:

I am creating one calculated member called [QualityKPI] for which I am writing MDX expression as below :

IIF([Measures].[Rows Count] <> 0 and [Measures].[Coefficient] <> 0, [Measures].[QualityFinal]/[Measures].[Coefficient],IIF([Measures].[Rows Count] <> 0 and [Measures].[Coefficient] = 0,[Measures].[Quality]/[Measures].[NumberofRecords],Null))

But how do I implement here WHERE condition ?

For ex., in my fact table I have a column [Rows Count] with two distinct values of 1 and 0.

I want to implement above MDX expression such that it only considers Rows Count=1 while doing the above calculation.

As an example I have a some records like below and the QualityKPI for each row should yields this results and the formula return exactly the same:

However on aggregation level (Lets say I want to calculate the QualityKPI for all products), I want to consider only records which their [Rows Count] is equal to 1 and therefore the aggregation should yields 0.5496 ((0.209948793 + 0 + 0 + 0) / (0.209948793 + 0 + 0 + 0.172043011 ))but with above formula it yields 0.18087 ((0 + 0.005869074 + 0 + 0 + 0.209948793 + 0 + 0 + 0) / (0 + 0.005869074 + 0 + 0.805352798 + 0.209948793 + 0 + 0 + 0.172043011 )).

Any help is appreciated.

Answer 1

In MDX, WHERE conditions are normally done on attributes, not on measures. So, the best approach would be to make an attribute from your Row Count column (i. e. making it part of a dimension).

Then, you could either leave your calculation as it is, enabling users to do their own where conditions, i. e. by dragging Row Count either into a filter or to rows o columns to see the breakdown by row count.

If you want to filter in your calculation itself, then to add the row count filter to the calculation, you would use tuples wherever you use just a measure currently, e. g. (assuming the attribute is called [Dim1].[Row Count] in the cube, and hence the member for row count 1 would be [Dim1].[Row Count].&[1]), you would use

([Measures].[Rows Count], [Dim1].[Row Count].&[1])  <> 0

instead of

[Measures].[Rows Count] <> 0

and similar for all the measures mentioned in your expression.

You can also use measures (assuming Row Count is one in your cube) for conditions, but that is more complicated to do in cube design and the MDX you need to write, e. g. you would have to think about aggregation to use, and is also much less efficient and hence results in slower queries.