Switch

This function creates a conditional execution path, where the flow of logic is determined by executing specified statements only if their "case" matches the supplied input value.

The switch function is most often used to swap out the logic used for building lists based on some changing value in the database. The other major use case is for creating 'variable lists' - which is a mechanism to swap out the list building logic based on user input via a parameter.

Syntax & Usage

Switch(variable value, statement 1, statement 2 .... statement n, default statement)
  • All given member elements must come from the same hierarchy.
  • The variable value is a numeric parameter.
  • The number of values in the given parameter determines the number of branches in the switch statement; a list must be configured on each branch.
  • When added to a query, the custom list will produce a slicer containing each branch of the switch statement.

Variable Lists

Variable lists, as described above, are a major use case for the switch function. There are two techniques for building Variable lists using UI wizards which are all based on implementing the switch function inside the logic.

Example

Start by adding the Switch block and select the required numeric parameter:

When the numeric parameter is selected, each of its values is added as a branch:

Configure a list on each branch. In this example, a list of all elements from the Product hierarchy is added to each branch. A Top Count block is then added to each list to filter by the top 25, 50, or 100 elements according to Net Profit:

{Switch([global].#[bbe2731c-f860-4a53-bcd8-e69c5a42684b],25,Top({AllMembers([products].[Product])},25,([measures].[data net profit])),50,Top({AllMembers([products].[Product])},50,([measures].[data net profit])),100,Top({AllMembers([products].[Product])},100,([measures].[data net profit])),{AllMembers([products].[Product])})}

When the switch list is added to the query, it is represented as a slicer, containing each of the switch statement's branches: