Alternative to If function in dimension for Pivot table

[femi_owoseni]

Hi,

I am using this expression as a dimension in a pivot table.

if([ChangeDate]=vLatestDate,[BusinessTitle] & ' (' & [Business Code] & ')',)

then I suppress Nulls and zeros and the results are as expected.

The issue is, the pivot table is now really slow and frequently times out. 

Is there another expression that will yield faster results?

PS. I am unable to make changes to the data model using the load editor at this time.

[sunny_talwar]

1) May be use set analysis instead of if statement here

Dimension

[BusinessTitle] & ' (' & [Business Code] & ')' 

Expression

Sum({<ChangeDate = {"$(=vLatestDate)"}>}Measure)

Note: The expression may not still work because I am not sure what is behind vLatestDate. May be you will need to add formatting to the variable so that it can be compared to ChangeDate field's format. But the idea stays the same that instead of using an if statement in your calculated dimension... use set analysis.

2) Create a new field in the script [BusinessTitle] & ' (' & [Business Code] & ')' -> since you mentioned that you don't want to make script changes, so this can be hold off until you are ready for script changes.

[femi_owoseni]

The expression is fine and works okay

My Expression: Min({<[Change Date]= {$(=chr(34))$(vLatestDate)$(=chr(34))}, [Country]={"United States"}>}[Total Sales]).

The dimension however needs to only include the most recent [Business Code] as well. The if statement I posted works but it is slow.

[sunny_talwar · Accepted solution]

If all your expressions have the set analysis to include the most recent change date, then your dimension will automatically show the value of the most recent change date. Basically, only those dimensions which meet the criteria of your expression are usually shown... is this not what you are seeing? Would you be able to share a sample to show your issue?

[femi_owoseni]

Ok I understand a little better now. I applied the same criteria on the other measures in the pivot table and it worked fine. No need for the if statement.