title: MDX Query IIF vs CASE Performance
body: <p>I have this MDX syntax which is not efficient when sliced using multiple dimension attributes. How can i improve the performance? what best to choose here IIF or CASE?</p>
  
  <pre><code>WITH 
    MEMBER [Measures].[test] AS 
      IIF
      (
        IsLeaf([Date].[MonthQtrYr].CurrentMember) = True
       ,IIF
        (
          [Measures].[1 Headcount] &gt; 0 AND [Measures].[1 Termcount] &gt; 0
         ,
          [Measures].[1 Termcount] / [Measures].[1 Headcount]
         ,null
        )
       ,IIF
        (
                  [Date].[MonthQtrYr].CurrentMember IS [Date].[MonthQtrYr].[All]
                AND 
                  [Date].[Month].CurrentMember IS [Date].[Month].[All]
              AND 
                [Date].[Quarter].CurrentMember IS [Date].[Quarter].[All]
            AND 
              [Date].[Week].CurrentMember IS [Date].[Week].[All]
          AND 
            [Date].[WeekbyYr].CurrentMember IS [Date].[WeekbyYr].[All]
         ,IIF
          (
            [Measures].[1 Headcount] &gt; 0 AND [Measures].[1 Termcount] &gt; 0
           ,
              [Measures].[1 Termcount]
            / 
              IIF
              (
                [TRegrets].[Regrets].CurrentMember IS [TRegrets].[Regrets].&amp;[With Regrets]
               ,(
                  [TRegrets].[Regrets].&amp;[Not Applicable]
                 ,[Measures].[1 Headcount]
                )
               ,[Measures].[1 Headcount]
              )
           ,null
          )
         ,IIF
          (
                [Date].[MonthQtrYr].CurrentMember
              IS 
                StrToMember
                (
                  "[Date].[MonthQtrYr].[Year].&amp;[" + Cstr([Measures].[MaxYear]) + "]"
                )
            OR 
                [Date].[Quarter].CurrentMember
              IS 
                StrToMember
                ("[Date].[Quarter].&amp;["
                    + 
                      StrToMember
                      (
                        "[Date].[Quarter].&amp;[Q" + Cstr([Measures].[PrevQtr]) + "]"
                      ).NextMember.Member_Caption
                  + "]"
                )
           ,StrToMember("[Date].[Month].&amp;[" + Cstr([Measures].[CurrMo]) + "]")
           ,[Date].[MonthQtrYr].CurrentMember.LastChild
          )
        )
      ) 
  </code></pre>