2 Replies Latest reply: Jun 1, 2010 5:20 PM by QuestionsQuestions

# Amend the formula used in an expression based upon the value in a dimension?

Hello,

Is it possible to change the formula used to evaluate and expression based upon the value in a dimension (in a straight table for instance)

KPI:

[KPI ID], [UseFormula]

1, Field1 + Field2

2, Field1

3, (Field 1 / Field2)

Values:

[KPI ID], [Field1], [Field2]

1, 2, 10

2, 4, 11

3, 5, 12

And then created a straight table with [KPI ID] as the dimension and [UseFormula] as the expression. What it does is produce a table exactly like KPI, i was hoping it would actually evaluate Field1 and Field2 and use their corresponsing values from the Values table. But i'm wrong. Is there a way of acheiving this, i've thought about using a variable, but with me wanting the formula to change from row to row in my table i don't think this will work - can anyone advise. Appreciate your help.

• ###### Amend the formula used in an expression based upon the value in a dimension?

You're better off with an If statement:

=If([KPI ID] = 1, [Field 1] + [Field 2], If([KPI ID] = 2, [Field 1], [Field 1]/[Field 2])) as [UseFormula]

• ###### Amend the formula used in an expression based upon the value in a dimension?

Cheers for this....