Working with Power Fx and Switch()

Power Fx is a low-code language that can be used in the Power Platform to implement complex logic without the need for a developer.  

Although Power Fx was created for business users and low code developers, it still requires specific syntax. Anyone who wants to use Power Fx needs to learn it. Power Fx can be used in different areas of the Power Platform, including Canvas Apps, Model-Driven App Commands, Power Apps Ideas, with more coming in the future. 

This post specifically focuses on using Power Fx in a Dataverse table.  

In the Dataverse, Power Fx is used to calculate the value of a formula-type column.  

Switch ()

This post mainly focuses on the Switch() function. The Switch() function compares an input value against multiple different cases and returns a value or performs an action based on the input value matching a case.  

Use Case 

A Dataverse table, Pet, stores the pets for clients of a pet boarding company. Pet contains a pet’s name, breed, and the type of animal the pet is. The pet boarding company decides to  group the pets based on the sounds they make to reduce clashing noises in the different housing sections. This is done by linking the type of animal to the sound they make. 

A new column, Sound, needs to be created in Pet to store the pet’s sound.  

The below table contains the different animal types and their corresponding sound: 

When a new pet is created, the Type would be entered, and our formula will automatically calculate Sound. For existing pets, Sound needs to be calculated without additional user data entry.  

Possible Solutions 

This post focuses primarily on the Switch() function, but the desired outcome can be performed with nested If() statements. A pseudo-code example using nested If() statements shown below: 

				
					If( 
    Type == Cat, 
    “Meow”, 
    If( 
        Type == Large Dog, 
        “Woof”, 
        If( 
            Type == Small Dog,
            “Yap”, 
            If( 
                Type == Snake,
                “Hiss”, 
                If( 
                    Type == Parakeet, 
                    “Squawk”, 
                    ”” 
                ) 
            ) 
        ) 
    ) 
) 
				
			

This nested If() statement would create our desired outcome, but for this specific use case, a Switch() function could also be used.

Example pseudo-code for the Switch() statement shown below:  

				
					Switch( 
    Type, 
    Cat, “Meow”, 
    Large Dog, “Woof”, 
    Small Dog, “Yap”, 
    Snake, “Hiss”, 
    Parakeet,“Squawk”, 
    "" 
) 
				
			

Although the same outcome occurs from both options, the Switch() function would be a better option than the nested If() statements in this specific scenario because of:  

  • Performance

    • The Switch() formula only requires evaluating a single function, whereas the If() statement requires evaluating 5 functions.
    • This would be more performant due to the lessened number of functions.   
  • Legibility

    • The Switch() function is easier to read than the nested If() statements 
    • For future maintenance, it would be simpler to add additional options to the Switch() function when compared to the nested If() statements.

Using Switch() in Power Apps 

The Switch() function is available in Power Fx and can be used in the Power platform. Although Switch() functions are relatively simple in concept, the exact syntax can be tricky.  The rest of this post focuses on the specific syntax of using a Switch() formula in a Dataverse table using a formula column type.   

Switch() on Text Columns 

This section details the syntax of using a Switch() function when our input parameter is a Text column.  

Format for a Switch() Function on a Text Column:  

				
					Switch( 
    Column_Name, 
    ”String Value 1”,“Result 1”, 
    "String Value 2”, “Result 2”, 
    “String Value 3”, “Result 3” 
    .... 
    “String Value x”,“Result X”, 
    ”Default Value” 
    ) 
				
			

Switch() formula on a Text column 

Given our above example for the table Pet, if Type is a text columnSound would contain the following Power Fx

				
					Switch(
    Type,
    "Cat","Meow",
    "Small Dog","Yap",
    "Large Dog","Woof",
    "Snake","Hiss",
    "Parakeet","Squawk"
) 

				
			

Since a text-type column doesn’t have strict restrictions on the values entered within it, our Switch() function cannot cover all possible values of the column we reference in the formula.

When allowing users to enter text, there are endless possibilities of things that can go wrong, for example:

  • Typos

    • Someone writes “Paraket” instead of “Parakeet”
    • Accounting for all possible typos in our switch formula is improbable, and expecting end users to never make a typo is unrealistic.
  • Ignorance of expectations

    • Entering “Dog” instead of “Large Dog” or “Small Dog”
    • Entering “Big Dog” instead of “Large Dog”
    • End users cannot see our formulas and may not be aware of the expected values.
  • Unhandled use cases

    • Someone gets a dart frog as a pet.
    • If an end user entered “Frog” or “Amphibian”, this would be a correct entry.
    • However, our formula does not account for this, and wouldn’t set the sound to “Ribbit” as it should.

Because of these reasons, it would be beneficial for Type to be a choice column instead of a text column.

Switch() on Choice Columns

Choice columns allow a user to select from a range of pre-set options. Choice columns can be global and non-global. Global choice columns are linked to a global choice, a list of options that can be referenced on multiple tables. Non-global choice columns contain a list of options specific to that column.

Format for a Choice Column in a Switch() Function:  

				
					Switch(
    field name,
    choice.selection1,	"Result 1",
    choice.selection2, "Result 2",
    choice.selection3,"Result 3",
    …
    choice.selectionX,”Result X”,
    "Default Result"
	  )

				
			

Switch on Global Choice Columns

The following formula applies when Type is a global choice column in the table Pet.

Type uses the global choice Animal.

Sound would be a formula-type column with the below formula:

				
					Switch(
    Type,
    Animal.Cat,"Meow",
    Animal.'Large Dog',"Woof",
    Animal.'Small Dog',"Yap",
    Animal.Snake,"Hiss",
    Animal.Parakeet,"Squawk"
)

				
			

Notice how the Animal values that contain a space are wrapped in single quotes. This applies to any reference whose name contains a space.

Below is the formula when Type is renamed to Animal Type:

				
					Switch(
        'Animal Type',
        Animal.Cat,"Meow",
        Animal.'Large Dog',"Woof",
        Animal.'Small Dog',"Yap",
        Animal.Snake,"Hiss",
        Animal.Parakeet,"Squawk"
        )

				
			

Since Animal Type contains a space in its name, it must be wrapped in single quotes in the formula.

In the above example, the global choice, Animal, and global choice column, Animal Type, have different names. If they shared the same name, our syntax would differ.

If the name of the global choice is changed to Animal Type, the formula in Sound will be in error.

The error text reads “Name isn’t valid. <choice selection> isn’t recognized.”

To fix this error, change the way that global choice is referenced.

When the global choice and the global choice column share the same name, the global choice option name must be wrapped in brackets, with an “@” sign inside the bracket before the name of the global choice.

An example is shown below:

				
					Switch(
        'Animal Type',
        [@'Animal Type'].Cat,"Meow",
        [@'Animal Type'].'Large Dog',"Woof",
        [@'Animal Type'].'Small Dog',"Yap",
        [@'Animal Type'].Snake,"Hiss",
        [@'Animal Type'].Parakeet,"Squawk"
        ) 


				
			

If a global choice and global choice column share the same name but contain no space, the format would be similar to the previous formula, but without the single quotes.

Below is the syntax when both the global choice and global choice column are named Type:

				
					Switch(
        Type,
        [@Type].Cat,"Meow",
        [@Type].'Large Dog',"Woof",
        [@Type].'Small Dog',"Yap",
        [@Type].Snake,"Hiss",
        [@Type].Parakeet,"Squawk"
        ) 


				
			

Switch on Non-Global Choice Columns

Non-global choice columns are less common than global choice columns; to use a Switch() formula on a non-global choice column, the syntax would change by adding the table name in parentheses after the choice name, example below:

				
					Switch(
        'Non-Global Choice Column',
        'Non-Global Choice Column (Pets)'.Cat,"Meow",
        'Non-Global Choice Column (Pets)'.'Large Dog',"Woof",
        'Non-Global Choice Column (Pets)'.'Small Dog',"Yap",
        'Non-Global Choice Column (Pets)'.Snake,"Hiss",
        'Non-Global Choice Column (Pets)'.Parakeet,"Squawk"
)
				
			

Final Results

Below is a screenshot of Pet using the formulas above.