1

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!

whytheq
  • 34,466
  • 65
  • 172
  • 267
olf
  • 860
  • 11
  • 23
  • are you developing within the cube, or are you creating a script that queries a cube i.e. are you creating a cube? – whytheq May 17 '18 at 13:12
  • I am making my calculated member in my multidimensional cube. Na Query from SSMS. – olf May 17 '18 at 13:18

3 Answers3

2

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 :-)

olf
  • 860
  • 11
  • 23
1

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
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • Almost - but not right there. The number I get is a "fixed" number. Meaning that all my Document Types is having the same percentage (which is the TOTAL number of completed Documents compared to the TOTAL number of Documents). I should mention that I make the calculation in my cube but I make my Query from Excel which is connected to the cube. – olf May 17 '18 at 13:40
  • ah - think i have an idea - do you have an attribute hierarchy for [Is Completed] ? try swapping that in instead of using the multi-level User Hierarchy Document – whytheq May 17 '18 at 13:42
  • Nope - there is no hierarchy. – olf May 17 '18 at 13:49
  • I don't think that is possible: User Hierarchies are created from Attribute Hierarchies: https://learn.microsoft.com/en-us/sql/analysis-services/multidimensional-models-olap-logical-dimension-objects/attributes-and-attribute-hierarchies?view=sql-analysis-services-2017 – whytheq May 17 '18 at 13:57
0

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

mxix
  • 3,539
  • 1
  • 16
  • 23