You can write formulas with conditions and different results based on condition evaluation using IIF and CASE functions. For simple IF / THEN / ELSE type of conditions use IIF(condition, if_true_expression, if_false_expression) (notice that there are two I letters in IIF), for exampleIIF(Measures.Profit > 0, 'Profit', 'Loss')
If there are many conditions then it is easier to use CASE function, for exampleCASE
WHEN Measures.Profit > 1000
THEN 'Big profit'
WHEN Measures.Profit > 0
THEN 'Small profit'
ELSE 'Loss'
END
If all conditions are comparisons of the same expression to different expected values then other CASE form can be used, for exampleCASE Time.CurrentMember.Level.Name
WHEN 'Month'
THEN Sum(LastPeriods(3), Measures.Store Sales)
WHEN 'Day'
THEN Sum(LastPeriods(90), Measures.Store Sales)
END
In IIF and CASE conditions standard comparison operators can be used (=, <, <=, <>, >, >=) as well as AND, OR and NOT operators as well as several specific operators: - IS returns whether two objects are the same, for example, Customers.CurrentMember IS Customers.DefaultMember (which will be true if Customers current member is default All Customers member)
- IN and NOT IN returns whether member is in set, for example Customers.CurrentMember IN Customers.USA.CA.Children \\\\\\\\\\\\\\\\\\\\\\\\\\
| 