SSAS expression. Getting more than one member of the same dimension in a tuple or excluding a member.

• stefan.quinn

  Grasshopper

  Points: 18

  More actions

  September 21, 2011 at 1:59 am

  #146349

  Hi

  Any help would be very appreciated! This is such a simple thing to do in SQL, but not MDX. I have tried the Exclude function, Exists, and Filter functions with no luck. I am still getting #VALUE returned when I process the cube.

  This is my statement (the second part is failing)

  iif([Account].[Account Type].CurrentMember IS [Account].[Account Type].&[Balance]

  ,([Posting Type].[Code].&[Normal],[Measures].[Ledger Balance End of Day])

  , {([Posting Type].[Code].&[Normal],[Measures].[Ledger Balance End of Day]),([Posting Type].[Code].&[Closing],[Measures].[Ledger Balance End of Day])}

  )

  Better yet, instead of just having the Normal and Closing members, I would just prefer to exclude the Closing member. NOT [Posting Type].[Code].&[Closing] doesnt work neither does -[Posting Type].[Code].&[Closing]

  HELP!

  thanks

• Kees Beimans

  SSC Eights!

  Points: 978

  More actions

  September 22, 2011 at 5:46 am

  #1385065

  Hi,

  Indeed your formula does not return a member in the second part of the Iif statemenet.

  To overcome your issue: Lets try the following

  Place the Aggregate function around your statement

  it looks like Aggregate ( iif ( ...... ) ).

  Other option is use aggregate in second part of Iif statement.

  iif ( ... , Aggregate ( { your set } ) )

  Regards Kees