You can define a formula that uses a conditional test or a series of conditional tests to determine the value for a member. Use the IIF function to perform a test with one else condition. You can nest IIF functions to create a more complex query.
The example specifies a formula for a member that represents the price the company must pay for credit card transactions, which includes a 5% charge. The following example assumes that the Credit Price member has been added to the Measures dimension of the ASOsamp.Sample database. Credit Price has the following formula, which adds 5% to Price Paid when the payment type is a credit card.
IIF ( [Payment Type].CurrentMember=[Credit Card], [Price Paid] * 1.05, [Price Paid] )
Use the CASE, WHEN, THEN construct to create formulas with multiple tests and else conditions.
The Filter function returns the tuples of the input set that meet the criteria of the specified search condition. For example, to establish a baseline (100) for all products, you can add a Baseline member and create a formula for it, as follows:
Count(Filter(Descendants([PersonalElectronics], [Products].Levels(0)),[Qtr1] > 100.00))