SSAS MDX calculation filter

Question

I would like to do something like this (tsql):

select saldi
where rek in (4,8)
 and rek5  <> 41111 
 and rek3  not in (a,b,c)

Following MDX, filter (4,8) <> 41111, is not working. If I remove the 41111 filter the measure works fine, just shows 4/8. Adding 41111 as a '<>' filter and the result is blank, not even 0.00.

THIS = IIF(
   ( [Rekeningen].[Rek n1].CurrentMember  = [Rekeningen].[Rek n1].&[4]
        or [Rekeningen].[Rek n1].CurrentMember  =  [Rekeningen].[Rek n1].&[8] )
   and [Rekeningen].[Rek n5].CurrentMember <> [Rekeningen].[Rek n5].&[41111]
,
   [Measures].[Saldi], 0.00
); 

I tried Google, but don't seem to get the keyword right. On social.msdn.microsoft.com a lot of reactions are marked as answer, but not for me this far. Deploying the ssas cube works fine, no errors.

Can anybody point me in the right direction? I tried Except, but that command doesn't seem to work with multile fields(rek3/rek5).

Answer 1

IS and NOT IS are used when comparing members. That gives us this:

THIS = 
  IIF
  (
      (
        [Rekeningen].[Rek n1].CurrentMember IS [Rekeningen].[Rek n1].&[4]
      OR 
        [Rekeningen].[Rek n1].CurrentMember is [Rekeningen].[Rek n1].&[8]
      )
    AND 
      [Rekeningen].[Rek n5].CurrentMember IS NOT [Rekeningen].[Rek n5].&[41111]
   ,[Measures].[Saldi]
   ,0
  );

Rather than 0 you should nearly always use null in cube script calcs or you'll kill performance:

THIS = 
  IIF
  (
      (
        [Rekeningen].[Rek n1].CurrentMember IS [Rekeningen].[Rek n1].&[4]
      OR 
        [Rekeningen].[Rek n1].CurrentMember is [Rekeningen].[Rek n1].&[8]
      )
    AND 
      [Rekeningen].[Rek n5].CurrentMember IS NOT [Rekeningen].[Rek n5].&[41111]
   ,[Measures].[Saldi]
   ,NULL
  );

I'm not convinced about these member names? [Rekeningen].[Rek n1].CurrentMember and [Rekeningen].[Rek n1].&[4]. Let me add the two alternatives that I think they might be:

1.

THIS = 
  IIF
  (
      (
        [Rekeningen].[Rek n1].CurrentMember IS [Rekeningen].[Rek n1].[Rek n1].&[4]
      OR 
        [Rekeningen].[Rek n1].CurrentMember is [Rekeningen].[Rek n1].[Rek n1].&[8]
      )
    AND 
      [Rekeningen].[Rek n5].CurrentMember IS NOT [Rekeningen].[Rek n5].[Rek n5].&[41111]
   ,[Measures].[Saldi]
   ,NULL
  );

or

2.

THIS = 
  IIF
  (
      (
        [Rekeningen].CurrentMember IS [Rekeningen].[Rek n1].&[4]
      OR 
        [Rekeningen].CurrentMember is [Rekeningen].[Rek n1].&[8]
      )
    AND 
      [Rekeningen].CurrentMember IS NOT [Rekeningen].[Rek n5].&[41111]
   ,[Measures].[Saldi]
   ,NULL
  );