CODEWITHSUNDEEP
ssasmdx
lolveley
lolveley

Reputation: 1709

MDX: add a condition to a measure

I would like to modify a measure in order to filter with a set: if the member belongs to the set, the measure returns the default measure, if not it returns 0.

Here is my attempt:

with
MEMBER spename AS [Dim misc].[SpePats].[Speciality Id].[{1037B3B2-EB0C-4DA3-BD9D-EB54F6CA92A5}].FirstChild.membervalue
SET spesSame AS filter([Dim misc].[SpePats].[Speciality Id].MEMBERS
,[Dim misc].[SpePats].CurrentMember.FirstChild.MemberValue = spename)--filter

MEMBER nb AS iif(IsEmpty(Intersect({[Dim misc].[SpePats].CurrentMember}
,spesSame))--intersect
,0
,Measures.[VParcours Nombre])

SELECT {nb} ON 0
,{[Dim misc].[SpePats].[Speciality Id].&[{1037B3B2-EB0C-4DA3-BD9D-EB54F6CA92A5}]
,[Dim misc].[SpePats].[Speciality Id].&[{1095C7D1-F24C-4E22-B2E4-4C2959E29FFD}]} ON 1
FROM [BDD PBM]

Here are some explanations: First, the name of a characteristic of some hospital services is retrieved. For example, the name can be 'gynecology'. Then, the set of speciality ids which name is the same as spename is created, hence spesSame is the list of speciality ids whose name is 'gynecology'.

What I need is to construct nb, defined by: if the currentmember of the hierarchy SpePats belongs to spesSame (intersect function), the result is the number of patients, otherwise it is 0, but the result is always the number of patients(in my request, in rows, the first Speciality id belongs to spesSame, not the second one).

thank you.

Upvotes: 1

Views: 117

Answers (1)

lolveley
lolveley

Reputation: 1709

ok, the syntax of the iif was wrong: the correct one is:

MEMBER nb AS iif(Intersect({[Dim misc].[SpePats].CurrentMember}
,spesSame).count=0--intersect
,0
,[Measures].[VPARCOURS Nombre])

Apparently, isempty tests if the cell is empty. If someone can explains further the reason behind it...

Upvotes: 1

Related Questions