CODEWITHSUNDEEP
ssasmdxcubeolap-cubeiif
user3114400
user3114400

Reputation: 11

IIF statement in calculated measure has unexpected behavior in certain cases

I have a set of status and substatus values in the cube browser and i want to get the max of insertion date for this pair only when the status=1 and substatus=1

I created a measure that has the following expression:

    IIF(([Trans Tasks FFMS Farah GVV].[Task Status].&[1],[Trans Tasks FFMS Farah GVV].[Task Sub Status].&[2]), 
    '-', Min([Measures].[Minimum Insertion DT]))

When a certain task has a status 1 and substatus 2 this works as expected but when a task does not have these values the value is calculated and displayed anyways!

What could be the cause of this behavior?

Upvotes: 1

Views: 1570

Answers (1)

FrankPl
FrankPl

Reputation: 13315

IIf needs a condition as the first argument, hence you would have to write 

IIF([Trans Tasks FFMS Farah GVV].[Task Status].CurrentMember IS [Trans Tasks FFMS Farah GVV].[Task Status].&[1]
     AND
    [Trans Tasks FFMS Farah GVV].[Task Sub Status].CurrentMember IS [Trans Tasks FFMS Farah GVV].[Task Sub Status].&[2]), 
    '-',
    [Measures].[Minimum Insertion DT]
   )

This is in contrast to SCOPE which needs a set as its argument.

Furthermore, I do not think you need the Min around the measure, a measure is defined at each combination of aggregated or unaggregated attributes.

Upvotes: 1

Related Questions