Issue with IIF

• HollywoodDeveloper

  SSC Enthusiast

  Points: 112

  More actions

  December 16, 2024 at 5:48 pm

  #4499176

  I am creating a new measure in an SSAS cube and encountering an issue. The result is being displayed in an Excel pivot table sourced from the cube.

  Here’s the code I’m working with:

  MDX

  CREATE MEMBER CURRENTCUBE.[Measures].[New Column]

  AS

  IIF(

  [Date].[Month Of Year].[1] OR [Date].[Month Of Year].[2] OR [Date].[Month Of Year].[3], -- Logical condition for Q1

  [Measures].[Column 1],

  [Measures].[Column 1] - ([Date].[Calendar Quarter].PREVMEMBER, [Measures].[Column 1])

  )

  Here’s what I’ve observed:

  If I hard-code values for both the true and false parts of the IIF condition, the result works as expected.

  If I hard-code only the false part of the condition, the result is also correct.

  However, when I include the expression [Measures].[Column 1] - ([Date].[Calendar Quarter].PREVMEMBER, [Measures].[Column 1]) in the false part, the measure always returns the false value for all 12 months, regardless of the condition.

  i have also tried

  [Date].[Month Of Year].CURRENTMEMBER.NAME IN {"1", "2", "3"},

  and

  [Date].[Month Of Year].CURRENTMEMBER IS [Date].[Month Of Year].[1]

  OR [Date].[Month Of Year].CURRENTMEMBER IS [Date].[Month Of Year].[2]

  OR [Date].[Month Of Year].CURRENTMEMBER IS [Date].[Month Of Year].[3]

  But all these gives me the same problem. Do you see anything I need to consider to yield the right result?

   

   

• Site Owners

  SSC Guru

  Points: 80356

  More actions

  December 17, 2024 at 6:10 pm

  #4499729

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.