Counts Based on Multiple condition in OLAP MDX in a single statement

Question

I am stuck with merging different MDX conditions into one single statement on MDX-SSAS.

Details: I have a cube (Acceptance) with following data as below:

Uniq_ID        Acceptance_Type        Responsible_Area
   1              Accepted                FrontEnd
   2              Denied                  BackEnd
   3              Accepted                FrontEnd
   4              Accepted                FrontEnd
   5              Denied                  BackEnd
   6              Accepted                BackEnd
   7              Denied                  BackEnd
   8              Accepted                FrontEnd
   9              Accepted                BackEnd

Logic:

(Count of UniqID where Acceptance_type = 'Accepted' and Responsible_Area = 'FrontEnd' / count(ALL))

At this point I have created 3 calculated members in my SSAS to get the Acceptance rate:

1.[Count of Accepted] --> Here I just take the count of all members WHERE Acceptance_type = "Accepted"

Code: ([AcceptanceType].[AcceptanceType].[AcceptanceTypeID].&[2],[Measures].[count])

2. [Count of Accepted with Responsible area Frontend] Here I add one more condition of Responsible_area = FrontEnd

Code: ([AcceptanceType].[AcceptanceType].[AcceptanceTypeID].&[2],[Measures].[Count of Accepted]) Note that I am using the measure created in 1

3.[Acceptance Rate]
**Code: IIF([Measures].[Count] = 0, NULL, [Count of Accepted with Responsible area Frontend] / [Measures].[Count])

I'm actually chaining the calculated members.

I want to merge all the points 1, 2 ,3 into a single and I'm not able to do so. I'm using Microsoft Visual Studio -SSAS.

#2 shows you mentioning AcceptanceTypeID again. Is that a typo? — GregGalloway

GregGalloway GregGalloway · Accepted Answer · 2016-12-03T01:30:54

You can do it in one calc like this. Note I made up the Frontend member reference because you didn't have an example in your question:

DIVIDE(
 ([AcceptanceType].[AcceptanceType].[AcceptanceTypeID].&[2],[ResponsibleArea].[ResponsibleArea].&[Frontend],[Measures].[count]),
 [Measures].[count]
)

Note that DIVIDE is just a shortcut for your IIf(count=0,,) code. It should work unless you have an older version of SSAS.

Counts Based on Multiple condition in OLAP MDX in a single statement

1 Answers