Home > Working with Planning Projects > Building an Application > Building Application Models > Building A Cube Model > Measure Settings > Measure Logic Functions > SWITCH Function

SWITCH Function
Last Updated 3/8/2012 9:55 PM

The SWITCH function enables a series of conditions to be tested and a different expression performed for based on the first satisfied condition.

The SWITCH statement defines condition test/true expression pairs to be defined such that we can perform multiple if tests in the same statement. 

    Format:  SWITCH (CONDITION1, IFTRUE1, CONDITION2, IFTRUE2, ETC....) 

    where 

        CONDITION(n) is the test condition to be evaluated to determine whether the IFTRUE(n) expression is executed. 

        The following logical operators can be used in the TEST_CONDITION: 

            EQ 
            LE 
            GE 
            LT 
            GT 
            NE 
            OR 
            AND 

        IFTRUE(n) is the expression that will be executed if CONDITION(n) is true.

EXAMPLE:

Assume we wish to trigger the calculation of a variable tax, whereby tax is 10% if the value is greater than 1000, and 25% if sales are less than or equal to 5000.

'tax'=switch('sales' GT2000,0.25*'sales', 'sales' GT 1000, 0.1*'sales')

Note that SWITCH will exit execution after the first valid condition is executed or, failing that, when all tests have been performed. It is important therefore to ensure the order of the tests is in the correct sequence.

For example in the above sample, had we tested the condition in the opposite sequence, the test for sales greater than 2000 would never have been reached as sales of greater than 2000 would always satisfy the sales greater than 1000 test.