If

Create a conditional execution path, where the flow of logic is determined by executing specified statements only if the logical expression is true, and other statements if the expression is false.

Syntax & Usage

If(logical expression, true expression, false expression)
  • The logical expression is a condition that evaluates to true or false.
    • If the logical expression is true, the true expression is evaluated and returned.
    • If the logical expression is false, the false expression is evaluated and returned.
  • Add the If block and set it's data point and its arguments.
  • Then add the required list to the true expression; this list will be returned if the logical expression evaluates to true.
  • Add the required list to the false expression; this list will be returned if the logical expression evaluates to false.

  • The true and false expressions should return the same type of result: either numeric values (and data points) or sets. Mixing result types can lead to errors.

Example

In this example, the If function is used to determine which elements from the Manufacturer hierarchy should be returned. The given data point is Acme, Cost:

After setting the data point, open the Insert Arguments window: set the method of comparison (for instance greater than, less than, or equal to), and the value to compare with (either a static value or a numeric parameter). Here the argument is greater than 500,000:

Configure the list that should be returned if the statement is true, and a second list that should be returned if the statement is false. Here, cost for Acme is greater than 500,000, a range list from Acme to Poomah will be returned. If cost for Acme isn't greater than 500,000, a range list from OId Balance to Woolson will be returned:

{If(([manufacturers].[Manufacturer].[Acme],[measures].[data Cost])>500000,Range([manufacturers].[Manufacturer].[Acme],[manufacturers].[Manufacturer].[Poomah]),Range([manufacturers].[Manufacturer].[Old Balance],[manufacturers].[Manufacturer].[Woolson]))}

When the custom set is added to the query, the second range list appears, because cost for Acme is not greater than 500,000: