MDX Calculated member with filter

1
votes

Being pretty new to MDX so this might be a rookie question. But have not been able to "translate" any answers to a solution!

I have a Measure which Counts my number of rows called [Measures].[Fact Count]

I also have a Dimension called [Document] which has a field called [Is Completed].

The [Document].[Is Completed] can have one of two values: [Yes] or [No].

The [Document] dimension does have several other fields, like [Document Type]

What I would like to ask a question like "How big a percentage of my different document types have been completed".

I have tried making a [Completed Count] like this:

([Measures].[Fact Count],
[Document].[Is Completed].&[Yes])

and then use it like:

[Measures].[Completed Count]/[Measures].[Fact Count]*100

But (of course) the [Completed Count] then takes all my completed documents instead of only those per e.g. [Document Type]

Hope this makes sense to someone!

3
ssasmdxcalculated-fieldcube-script
are you developing within the cube, or are you creating a script that queries a cube i.e. are you creating a cube? – whytheq
I am making my calculated member in my multidimensional cube. Na Query from SSMS. – olf

3 Answers

2
votes

Ah, got the solution!

Since my Dimension contains "Yes" or "No" values, I can convert them to 1 and 0 and add them to my [Measures] as [Measures].[Is Completed Sum]. And as the name says: I'll sum the values.

Then the calculation is straight forward:

[Measures].[Is Completed Sum]/
[Measures].[Fact Count]*100

or if I will make sure not to divide by 0:

iif(
[Measures].[Fact Count] = 0,
null,
[Measures].[Is Completed Sum]/
[Measures].[Fact Count]*100
)

I will still keep my Yes/no's in the Dimension as well since I can use them as filters when needed.

whytheq, thank you very much for all your input! It was very valuable and made my brain spin in the right direction :-)

1
votes

Maybe it is the other side that needs to be more explicit?

(
 ([Measures].[Fact Count],
  [Document].[Is Completed].&[Yes])
/
 ([Measures].[Fact Count],
  [Document].[Is Completed].[All])
)
*100

If you have an attribute hierarchy for Is Completed try using that - probably like this...

(
 ([Measures].[Fact Count],
  [Is Completed].[Is Completed].[Is Completed].&[Yes])
/
 ([Measures].[Fact Count],
  [Is Completed].[Is Completed].[All])
)
*100

or this

(
 ([Measures].[Fact Count],
  [Is Completed].[Is Completed].&[Yes])
/
 ([Measures].[Fact Count],
  [Is Completed].[All])
)
*100

If you change the measure to the following and put Yes | No ON COLUMNS do you start to get the correct results?

(
 ([Measures].[Fact Count])
/
 ([Measures].[Fact Count],
  [Document].[Is Completed].[All])
)
*100
0
votes

Maybe SCOPE it. (stole the code from whytheq, i think hes got the right answer)

SCOPE ([Document].[Is Completed].&[Yes])
THIS = (
 ([Measures].[Fact Count],
  [Document].[Is Completed].&[Yes])
/
 ([Measures].[Fact Count],
  [Document].[Is Completed].[All])
)
*100
END SCOPE

Regarding

Since my Dimension contains "Yes" or "No" values, I can convert them to 1 and 0 You could improve your model and add a new Column Value to your Dimension.

And actually get the 1 and 0 with just < Member>.VALUE