Calculated Dimension

[#1]

Hi,

I am having a few performance issues after adding in a number of new dimensions/sheets to my application.  I have 6 sheets with one Pivot table graph on each sheet.  I use three calculated dimensions to determine which side label gets displayed in each pivot ( 3x dimensions /side lables).  Below is an example of one calculated dimension that I am using:

 = IF(Segment.Dimension = 1, DATA.ORG_1_S,
IF(Segment.Dimension = 2, DATA.MARK_1,
IF(Segment.Dimension = 3, DATA.GEN_S, 
IF(Segment.Dimension = 4,DATA.FU_N,
IF(Segment.Dimension = 5, DATA.ASS_L,
IF(Segment.Dimension = 6, DATA.PORT_T, '-')))))) 

Segment.Dimension is just a simple list box - when Dimension 1 is selected the column ORG_1_S  gets displayed etc.

I have a few similiar multiple IF statements across all 6 pivots which is probably one reason for the poor performance.  However, I was thinking if I replaced the multiple IF statements with Set Analysis it may improve the performance?  I would really appreciate if someone can post the syntax to use

thank you.

[hic · Accepted solution]

The nested if that you have can be much faster executed if you use the pick() function.

Further, a calculated dimension is evaluated every time you click. You should consider defining this dimension in the script already. It is almost always a lot faster that way.

HIC

[#3]

Thanks HIC, I have used the Pick and Match Functions and it operates nicely..it probably has made it slightly quicker.  I will look into including this dimension (amongst others) in the script.